case statement in JOIN - 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

Transact-SQL (2000)

case statement in JOIN

Author

Topic

vinuvt
Starting Member

1 Post

Posted - 2005-07-29 : 04:51:23

Hi all,
Is it possible to use CASE statement in JOIN condition. i mean based on a colums value i want to join to two different tables, ie if value=1 then inner join to Table 1
value 0 then inner join to table 2

regards

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2005-07-29 : 05:19:35

SELECT a.CreationDate, CASE WHEN a.Value = 1 THEN b.Name ELSE c.Name END
FROM tableA a
LEFT JOIN tableB b ON b.Id = a.Value
LEFT JOIN tableC c ON c.Id = a.Value

BOL give you information about LEFT JOIN (BOL = Books Online = F1 in query analyzer)

If column Value in table A is 1 then use values from table b
If column Value in table A is other then 1 then use values from columns in table c.

Henri
~~~~
The envious praises me unknowingly

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-29 : 05:21:52

No I think it is not possible
The only way is

If value=1
query with inner join table1
else
query with inner join table2

Madhivanan

Failing to plan is Planning to fail

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-07-29 : 05:49:31

quote:
Originally posted by henrikop

SELECT a.CreationDate, CASE WHEN a.Value = 1 THEN b.Name ELSE c.Name END
FROM tableA a
LEFT JOIN tableB b ON b.Id = a.Value
LEFT JOIN tableC c ON c.Id = a.Value

BOL give you information about LEFT JOIN (BOL = Books Online = F1 in query analyzer)

If column Value in table A is 1 then use values from table b
If column Value in table A is other then 1 then use values from columns in table c.

Henri
~~~~
The envious praises me unknowingly

henrikop's reply is good. You can specifically display a different field from different tables that satisfy your case statement.

you could also do the IF. if your writing it on a procedure.

Want Philippines to become 1st World COuntry? Go for World War 3...

Kristen
Test

22859 Posts

Posted - 2005-07-29 : 07:35:03

I think you'd need to widen it to:


SELECT a.CreationDate, CASE WHEN a.Value = 1 THEN b.Name ELSE c.Name END
FROM tableA a
LEFT JOIN tableB b ON b.Id = a.Value AND a.Value = 1
LEFT JOIN tableC c ON c.Id = a.Value AND a.Value <> 1

otherwise a happen-stance a.Value that was matched in both TableB and TableC could create unnecessary rows (not in this example, but maybe in a more complex one)

The you could just COALESCE the columns to get answer values I suppose:

SELECT a.CreationDate, COALESCE(b.Name, c.Name) AS [Name] ...

which might be easier than writing a bunch of CASE statements if there are lots of them

Do TableB and TableC basically have the same structure? If so any chance of using a FULL OUTER JOIN?

Kristen

Jakubd
Starting Member

1 Post

Posted - 2010-01-15 : 03:49:37

Thanks Kristen and henrikop for the solution, works well :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-15 : 04:18:02

quote:
Originally posted by Jakubd

Thanks Kristen and henrikop for the solution, works well :)

Feedback after 5 years

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-15 : 05:11:53

Shall I publish my Y3K solution already?

henrikop
Constraint Violating Yak Guru

280 Posts

Posted - 2010-01-17 : 03:58:26

Of course!! After a year or five you'll still get some response.

Henri Koppen

~~~~
'Tis better to be silent and be thought a fool, than to speak and remove all doubt.

Abraham Lincoln

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources