3 tables joined - 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)

3 tables joined

Author

Topic

ultraman
Starting Member

18 Posts

Posted - 2004-12-13 : 12:18:11

Hi all !

This one is a "newbie" one I'd say. I have one master table with a "BatchNo" field that is linked to 2 child tables by this same field. So for a single BatchNo, you can have different records in both child tables. I would like to get the list of all records for both table for a given BatchNo.

Exemple :

MasterTable.BatchNo = 'BN04071'

ChildTable1
BatchNo|JobNo
BN04071|01
BN04071|02

ChildTable2
BatchNo|JobNo
BN04071|03
BN04071|04

ChildTable1 have 2 records with ChildTable1.BatchNo = 'BN04071'
ChildTable2 have 144 records with ChildTable2.BatchNo = 'BN04071'

I want a list of al the 146 records. I tried a simple INNER JOIN query, but I only got a product (288 records)...

--------------
Ultraman

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-13 : 12:24:45

select ct.type, mt.BatchNo, ct.JobNo
from MasterTable mt
join (select type = 'c1', BatchNo, JobNo from ChildTable1 union all select type = 'c2', BatchNo, JobNo from ChildTable2) ct
on mt.BatchNo = ct.BatchNo

or

select type = 'c1', mt.BatchNo, ct.JobNo
from MasterTable mt
join ChildTable1 ct
on mt.BatchNo = ct.BatchNo
union all
select type = 'c2', mt.BatchNo, ct.JobNo
from MasterTable mt
join ChildTable2 ct
on mt.BatchNo = ct.BatchNo

==========================================
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.

ultraman
Starting Member

18 Posts

Posted - 2004-12-13 : 13:25:37

That's good ! Thanks a lot !

--------------
Ultraman

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources