4 table join - 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 2005 Forums

Transact-SQL (2005)

4 table join

Author

Topic

DMarmolejos
Yak Posting Veteran

65 Posts

Posted - 2008-07-18 : 15:30:59

Table1 T1
cola
colb
colc

Table2 T2
cola
cole

Table3 T3
cola
colf

Table4 T4
colc
colz

When I try to group all of them I get repeating rows..

This is what I had:
Select
FROM Table1 T1, Table2 T2, Table3 T3, Table4 T4
WHERE T1.colz = T4.colz AND T2.cola = T3.cola AND T3.cola = T1.cola

My question is how is the correct way to join these tables so I dont get repeating rows?

Any help is appreciated
Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-18 : 15:32:36

What is your question? Could you post sample data and expected output?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-07-18 : 15:37:56

You've edited your post since I've posted mine, so I'll respond to your edit.

You are getting repeating rows as there are multiple rows satisfying the join condition. To prevent this, you can use group by although it would be best if you showed us some sample data and expected output so that we can help you better.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-07-18 : 15:44:04

T1 does not have "colz" column. Post the query as you run it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-19 : 01:48:18

posting some sample data from your tables will also help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-19 : 02:29:31

You can

1) Add a DISTINCT keyword
2) Rewrite all JOINs as derived tables to eliminate duplicate data

SELECT		*
FROM		Table1 AS t1
INNER JOIN	Table3 AS t3 ON t3.colA = t1.colA
INNER JOIN	Table2 AS t2 ON t2.colA = t3.colA
INNER JOIN	Table4 AS t4 ON t4.colC = t1.colC

Please remember we do not have access to you system,
and based on the very little information you have given us, we have no other chance than guessing.
WE don't even know what is stored in the tables!

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources