Criteria in a 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 2005 Forums

Transact-SQL (2005)

Criteria in a join

Author

Topic

webappguru
Starting Member

10 Posts

Posted - 2009-02-05 : 19:31:52

Is there any way to do a Case statement within a join

This is not the real example, but it explains my need (NO, I am not hungry

So you have you basic join:

SELECT * FROM Apple a
Join Pear p on a.FruitId = p.FruitId

Well what if you only wanted to put a case statement with the and

AND
Case when Dessert = FruitSalad then join fruitsalad f on f.fruitid = a.fruitId
Case when Dessert = PearCobbler then join peachCobbler p on f.fruitid = p.fruitiD

So the join is different based on what the dessert is.

I could easily do an if first before the whole procedure and write one SQL statement for each type of Dessert in the If/Case, but is there a way to combine a Case/If with a JOIN/AND

"I live for programming"
www.aliabidhusain.net

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2009-02-05 : 21:55:58

This may help you:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118727
http://dotnet.org.za/auratius/archive/2005/01/12/13176.aspx
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24048579.html
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=91&threadid=91587&enterthread=y

This has made me hungry, better go and get lunch:)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-05 : 23:22:10

try like this
select a.col1,a.col2,...
Case when a.Dessert = 'FruitSalad' then f.fruitid
when a.Dessert = 'PearCobbler' then p.fruitiD end as value
from
Apple a
Join Pear p on a.FruitId = p.FruitId
join fruitsalad f on f.fruitid = a.fruitId
join peachCobbler p on f.fruitid = p.fruitiD

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-02-06 : 01:42:40

try this, but it not good for performance

SELECT * 
FROM Apple a, fruitsalad f, peachCobbler p
WHERE a.fruitId = 
				(
					CASE	WHEN a.Dessert = 'FruitSalad' THEN f.fruitid 
							WHEN a.Dessert = 'PearCobbler' THEN p.fruitiD
					END
				)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 09:42:36

[code]SELECT * FROM Apple a
Join Pear p on a.FruitId = p.FruitId
left join Fruitsalad f on f.fruitid = a.fruitId and Dessert = 'FruitSalad'
left join peachCobbler p on f.fruitid = p.fruitiD and Dessert = 'PearCobbler'[/code]

webappguru
Starting Member

10 Posts

Posted - 2009-02-06 : 10:04:02

WOW! This forum is great. Thank you for all your helpful hints.. now I need to go through and see how to apply it to my real world issue. Sorry if I have made anyone hungry.

"I live for programming"
www.aliabidhusain.net

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-06 : 10:06:55

you're welcome

webappguru
Starting Member

10 Posts

Posted - 2009-02-06 : 10:41:36

quote:
Originally posted by harlingtonthewizard
This has made me hungry, better go and get lunch:)

So did you have the peach cobbler or fruit salad?

"I live for programming"
www.aliabidhusain.net

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-06 : 10:50:06

quote:
Originally posted by webappguru

quote:
Originally posted by harlingtonthewizard
This has made me hungry, better go and get lunch:)

So did you have the peach cobbler or fruit salad?

"I live for programming"
www.aliabidhusain.net

I love Fruit salad

. Never tried Peach Cobbler.

webappguru
Starting Member

10 Posts

Posted - 2009-02-06 : 10:57:48

It was used as an example to solve a much more perplexing problem. I wil post my final solution when its done....

quote:
Originally posted by sodeep

quote:
Originally posted by webappguru

quote:
Originally posted by harlingtonthewizard
This has made me hungry, better go and get lunch:)

So did you have the peach cobbler or fruit salad?

"I live for programming"
www.aliabidhusain.net

I love Fruit salad . Never tried Peach Cobbler.

"I live for programming"
www.aliabidhusain.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources