Joining Three Tables - 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)

Joining Three Tables

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2009-01-19 : 05:28:50

I need some help joining three tables:
All three tables have a column called AuditID (but not unique on two of them)

Tab1 (Unique AuditID) has one row per audit.
Tab2 has 24 rows attached to the audit no in table 1
and Tab3 has two rows attached to auditID in table 1

Basically I need to return all the rows from all tables attached to the unique row in table1

I have tried:

SELECT dbo.Tab1.AuditID
FROM dbo.Tab1 JOIN
dbo.Tab2 ON dbo.Tab1.AuditId = dbo.Tab2.AuditId JOIN
dbo.Tab3 ON dbo.Tab1.AuditId = dbo.Tab3.AuditId
WHERE (dbo.Tab1.AuditId = 5922)

But it returns the incorrect amount of rows (48).....

Kind Regards

Pete.

petek
Posting Yak Master

192 Posts

Posted - 2009-01-19 : 05:37:53

Hi all,

I have got the result i wanted:

select dbo.Tab1.AuditId
from dbo.Tab1
where AuditId = 5922
UNION ALL
select dbo.Tab2.AuditId
from dbo.Tab2
where AuditId = 5922
UNION ALL
select dbo.Tab3.AuditId
from dbo.Tab3
where AuditId = 5922

is it possible to delete all rows from the three tables with the auditID = 5922

Kind Regards

Pete.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-19 : 05:44:49

if foriegn key relationship is there b\w tab3,tab2,tab1
so
delete tab3 where auditid = 5922
delete tab2 where auditid = 5922
delete tab1 where auditid = 5922

petek
Posting Yak Master

192 Posts

Posted - 2009-01-19 : 05:48:37

Hi bklr thanks for your reply,

that query is fine to delete them record by record but i have multiple uniqiue ids to delete from tab1 and all assosicated records from the other two tables.

Kind Regards

Pete.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-19 : 06:02:56

delete tab3 where auditid in (5922,5394,5923,5924.........)
delete tab2 where auditid (5922,5394,5923,5924.........)
delete tab1 where auditid (5922,5394,5923,5924.........)

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-24 : 01:15:22

Or If you have control over schema, you could simply modify all foreign key definition with ON DELETE CASCADE feature. When you specify this while creating a foreign key, any record deletion in the parent table will automatically remove the related records in the child tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources