how to write 3 tables with 3 join conditions in Qu

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

General SQL Server Forums

New to SQL Server Programming

how to write 3 tables with 3 join conditions in Qu

Author

Topic

dhani
Posting Yak Master

132 Posts

Posted - 2010-02-01 : 20:08:11

Hello All,

i am using 3 tables in my query
Table A, Table B, Table C

A is the super table it has a column name --> objectid, which can find in both b & c sub tables

so i wrote query as below

select
con.objectid as CID,
count(TableObjIns.LID) as LCount
from TableContra as Con left join
TableObj on TableObj.objectid = Con.objectid left join
TableObjIns on TableObjIns.attr1538=con.attr1485
group by con.objectid
order by 2 desc

now i have a little problem in my answer, one of team mate suggest me, i have to join Table A to Table B then Table A to Table c finally Table B to Table C,

so i tried below way, but i didn't get my result, but she suggested me i need to write a join (either Table A to B or B to C) then join to other table so for that i tried below but i am getting errors

could you please tell me how to write a query (ex Table A, B ,C) Ato B in one query and (b to c) in one query then join together like...

i tried below query (which results an error)

select
con.objectid as CID,
count(TableObjIns.LID) as LCount
from TableContra as Con left join
TableObj on TableObj.objectid = Con.objectid left join
TableObjIns on TableObjIns.attr1538=con.attr1485 left join
TableObjas b on b.objectid = TableObjIns.objectid
group by con.objectid
order by 2 desc

Please Help Me sir

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2010-02-01 : 23:13:53

try like this


select
	con.objectid as CID,
	count(TableObjIns.LID) as LCount
from
	 TableContra as Con 
left join
	TableObj AS o on o.objectid = Con.objectid 
left join
	TableObjIns AS i on i.attr1538=con.attr1485 and i.objectid = Con.objectid 
group by con.objectid
order by 2 desc

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-02-01 : 23:15:47

What is the error you get?

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources