Multiple inner join. Help! - 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)

Multiple inner join. Help!

Author

Topic

elektrobank
Starting Member

15 Posts

Posted - 2003-04-04 : 18:33:02

I have a query like this:

select * from tableA
LEFT JOIN tableB ON tableB.mainID = tableA.id
LEFT JOIN tableC ON tableC.mainID = tableA.id

Where tableA is my main table and tableB and tableC can have 0 or more records that reference tableA. This is easy and works just fine. Now I am trying to add an additional table to the mix.

I want to add a tableD which I want to join to tableB and tableC on a condition. So I want to do something like this:

select * from tableA, tableD
LEFT JOIN tableB ON (tableB.mainID = tableA.id AND
tableB.otherID = tableD.ID AND tableD.name = "something")
LEFT JOIN tableC ON tableC.mainID = tableA.id AND
tableC.otherID = tableD.ID AND tableD.name = "something")

Of course this doesn't work, but hopefully demonstrates what I am trying to do. Basically just join the 4 tables but return nulls when the tableA doesn't have links to both tableB and tableC at the same time. How can I write this query to do that?

Thanks...

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-04 : 18:54:27

Only want tableD if both B and C entries?

select *
from tableA
LEFT JOIN tableB ON tableB.mainID = tableA.id
LEFT JOIN tableC ON tableC.mainID = tableA.id
LEFT JOIN tableD
ON tableC.otherID = tableD.ID
AND tableB.otherID = tableD.ID
AND tableD.name = "something"

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

elektrobank
Starting Member

15 Posts

Posted - 2003-04-04 : 19:36:38

This won't work. It returns duplicates and groups table B and C. Lets say we have a record in tableA with 3 records in tableB that reference it and 2 in tableA that reference it. When I run my query I want to get back 5 results, each having tableA and either tableB OR tableC joined to it. How can I do that?

quote:

Only want tableD if both B and C entries?

select *
from tableA
LEFT JOIN tableB ON tableB.mainID = tableA.id
LEFT JOIN tableC ON tableC.mainID = tableA.id
LEFT JOIN tableD
ON tableC.otherID = tableD.ID
AND tableB.otherID = tableD.ID
AND tableD.name = "something"

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-05 : 08:17:53

To do that you will need a union - one query to get back the recs joined to b and the other those joined to c.
Think this will work

select *
from tableA
LEFT JOIN tableB ON tableB.mainID = tableA.id
LEFT JOIN tableC ON 1 = 0
LEFT JOIN tableD
ON tableB.otherID = tableD.ID
AND tableD.name = "something"
union
select *
from tableA
LEFT JOIN tableB ON 1 = 0
LEFT JOIN tableC ON tableC.mainID = tableA.id
LEFT JOIN tableD
ON tableC.otherID = tableD.ID
AND tableD.name = "something"

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources