conditional joins in select statement

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)

conditional joins in select statement

Author

Topic

svibuk
Yak Posting Veteran

62 Posts

Posted - 2014-02-19 : 07:38:22

SELECT t1.id,name, from table1 t1 INNER join table t2 on t1.id=t2.pid
case when type='A' then
Inner join table3 t3 on t1.id=t3.id
else
Inner join table4 t4 on t1.id=t4.id
END

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-02-19 : 07:59:18

[code]
SELECT t1.id,name, from table1 t1
INNER join table t2 on t1.id=t2.pid

LEFT JOIN table3 t3 on t1.id=t3.id AND TYPE='A'
LEFT JOIN table4 t4 on t1.id=t4.id AND TYPE<>'A'
[/code]

sabinWeb MCP

svibuk
Yak Posting Veteran

62 Posts

Posted - 2014-02-19 : 23:51:59

quote:
Originally posted by stepson

SELECT t1.id,name, from table1 t1
INNER join table t2 on t1.id=t2.pid

LEFT JOIN table3 t3 on t1.id=t3.id AND TYPE='A'
LEFT JOIN table4 t4 on t1.id=t4.id AND TYPE<>'A'

cant use it in this way
table3 t3
table4 t4
both this tables can have same id but the type will be different

eg :

id Name Type
1 abc A
2 xyz A
1 mno D
3 bbb A

the data can be in this way
sabinWeb MCP

marcusn25
Yak Posting Veteran

56 Posts

Posted - 2014-03-04 : 18:37:19

Would this not solve it for you?

SELECT t1.id,name, from table1 t1
INNER join table t2 on t1.id=t2.pid

LEFT JOIN table3 t3 on t1.id=t3.id AND t1.TYPE='A' AND t3.TYPE='A'
LEFT JOIN table4 t4 on t1.id=t4.id AND t1.TYPE<>'A' AND t4.TYPE<>'A'

Marcus

I learn something new everyday.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources