Join without Union - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Join without Union

Author

Topic

skillile
Posting Yak Master

208 Posts

Posted - 2002-12-22 : 22:21:19

Here is my result set:
count id type
9 2 50
6 3 75
4 1 25

I have 3 tables:
Links = 50
Articles = 75
Programs = 25

From the result set at the top I need to join the id to the correct table. So join link data to the 50, article to the 75... .

I want to return all rows without using a UNION. Is there a way to either case the INNER JOIN statement or am I missing something.

Thanks

slow down to move faster...

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-12-22 : 23:39:33

Hi

You could LEFT JOIN all your tables on with a case statement

i.e.

LEFT JOIN Links ON Links.ID = MyTable.ID and MyTable.Type = 50
LEFT JOIN Articles ON Articles.ID = MyTable.ID and MyTable.Type = 75
LEFT JOIN Programs ON Programs.ID = MyTable.ID and MyTable.Type = 25

then use a CASE statement in your select to display the results

i.e.

SELECT Case Type
WHEN 25 THEN Programs.Name
WHEN 50 THEN Links.Name
WHEN 75 THEN Articles.Name
END as Name

etc

Hope that helps

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources