troubleshooting Question

sql join -

Avatar of shmz
shmzFlag for United States of America asked on
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
23 Comments1 Solution163 ViewsLast Modified:
Hi,

I have some tables with the following relationships:

table1               M:1            table2                         1:M            table3                          M:1           table4              
__________                            __________                                     __________                                      __________
tbl1_ID (PK)                        tbl2_ID (PK)                                   tbl3_ID (PK)                                  tbl4_ID (PK)
tbl2_ID (FK)                                                                                tbl2_ID (FK)                                  
                                                                                                    tbl4_ID (FK)

I am trying to write a join between the tables as follow:

select distinct tblx.x                                              
  , fo.col3                            
 ,o.NAME                <-------when I add this column, all result set being duplicated by the number of records returned from table1                
from table4                     as fo                                                                                                    
left join  table3               as th               on  fo.tbl4_ID(PK)  = th.tbl4_ID(FK)
left join table2                as tw              on  tw.tbl2_ID(PK) = th.tbl2_ID(FK)
join table1                       as o                on  o.tbl2_ID(FK) = tw.tbl2_ID(PK)

as I mentioned above, when I add column o.Name, all result set being duplicated by the number of records returned from table1 .
e.g: the result look like this:

 some object         year       NAME
pot                          2009      x
pot                          2009      y
pot                          2009      z
plate                       2005      x
plate                       2005      y
plate                       2005      z

instead of this:
pot                          2009      x
plate                       2005      z

I appreciate your help
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 23 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 23 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros