Avatar of Sh M
Sh M
Flag for United States of America asked on

sql join -

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
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Sh M

8/22/2022 - Mon
teebon

Hi shmz,

How you decide to exclude :

pot                          2009      y
pot                          2009      z

plate                       2005      x
plate                       2005      y

Is it based on a counting column on one of the table?
Sh M

ASKER
I did not exclude, I do not expect to see them in the result set. i.e. I expect the join correctly figure what is the correct Name for that record.
Sh M

ASKER
The join should figure that: pot    2009 matches with   x  in table1.Name column. it should not match the record with all records from table1.NAME which are (x,v and z)
Your help has saved me hundreds of hours of internet surfing.
fblack61
Sh M

ASKER
this is just an example to say what I expect to get from query above if it works correctly.
teebon

May I know what is the logic to decide whether it is the correct name for the object? If you can explain which column corresponds to which table it will be helpful e.g. [object name] = fo.col3 ? [year] = th.col2?
Sh M

ASKER
please don't mix the query with the arbitrary example I gave just to describe the problem. they are not related. I appreciate if you focus on the query.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
teebon

Hi shmz,

As table 1 is having M:1 relationship to table 2, the results will always show multiple rows for table 1. Unless you have a separate logic to limit or map table 1 column to the corresponding column of table X, then you will be able to filter it.
Sh M

ASKER
I think so.
I am open to totally change the structure of the query if you know the solution?
teebon

Suggest to have your Name column in Table 2 or Table 4.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Sh M

ASKER
I can't change the database!
I can only change the query.
teebon

Hi shmz,

Please share example data for each of the tables so that further advice can be provided.
Vitor Montalvão

Why are you using LEFT JOIN instead of INNER JOIN?
What this code returns?
select distinct tblx.x                                              
   , fo.col3                            
  ,o.NAME                
 from table4                        as fo                                                                                                     
 inner join  table3               as th               on  fo.tbl4_ID  = th.tbl4_ID 
 inner join table2                as tw              on  tw.tbl2_ID = th.tbl2_ID 
 inner join table1                as o                on  o.tbl2_ID = tw.tbl2_ID

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sh M

ASKER
Actually, doesn't matter if I use the left join or not, returns same number of records.
Sh M

ASKER
I expect 11 records but it returns 33 records.
Looks like each records is repeated 3 times if that helps.
Vitor Montalvão

Then you really need to provide the logic why are x and z chose instead of y.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Sh M

ASKER
So correction to example I provided:

 some object         year       NAME
pot                          2009      X
pot                          2009      X
pot                          2009      X

plate                       2005      Y
plate                       2005      Y
plate                       2005      Y

I expect to see:
pot                          2009      X
plate                       2005.      Y
Vitor Montalvão

With that sample the DISTINCT works. Something still missing.
Sh M

ASKER
Yes, you are right. The problem is another tablex.
Tablex is in relation with table4.

It is:
table4       1:m      tablex
_______.                _______
Tbl4_ID.                 Tblx_ID
                               Tbl4_ID(FK)
                                DateFrom
                                DateTo
                                Status



So in the query they have inner joined by fk.
If I look at tablex for a particular record I see there are 3 records with different 'fromdates'.
Therefore it generated all records attached to each dates and hence records are tripled.

Select distinct tblx.tbl4_id(FK)
,tblx.status
, tblx.date to
,tblx.date from
  , fo.col3                            
 ,o.NAME                              
from table4                     as fo        
Join tablex.                     As tblx
On tblx.tbl4_id(FK) = fo.tbl4_id                                                                                            
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)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Vitor Montalvão

Ok. Then you just need which of those 3 records you want (the one with older date or the newest one?). Or even another criteria.
ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
teebon

I assume you need to filter the tblx based on status? if so, please use the following:

Select distinct tblx.tbl4_id(FK)
,tblx.status
, tblx.date to
,tblx.date from
, fo.col3                            
,o.NAME                              
from table4                     as fo        
Join tablex                     As tblx
On tblx.tbl4_id = fo.tbl4_id  and tblx.status = 'xxx'                                                                                       
left join  table3               as th               on  fo.tbl4_ID  = th.tbl4_ID
left join table2                as tw              on  tw.tbl2_ID = th.tbl2_ID
join table1                       as o                on  o.tbl2_ID = tw.tbl2_ID

Open in new window

Sh M

ASKER
thanks all.
We had to modify the database and additional tables.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Sh M

ASKER
Thanks