Link to home
Start Free TrialLog in
Avatar of Sh M
Sh MFlag 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
Avatar of teebon
teebon
Flag of Singapore image

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?
Avatar of 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.
Avatar of 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)
Avatar of Sh M

ASKER

this is just an example to say what I expect to get from query above if it works correctly.
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?
Avatar of 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.
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.
Avatar of Sh M

ASKER

I think so.
I am open to totally change the structure of the query if you know the solution?
Suggest to have your Name column in Table 2 or Table 4.
Avatar of Sh M

ASKER

I can't change the database!
I can only change the query.
Hi shmz,

Please share example data for each of the tables so that further advice can be provided.
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

Avatar of Sh M

ASKER

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

ASKER

I expect 11 records but it returns 33 records.
Looks like each records is repeated 3 times if that helps.
Then you really need to provide the logic why are x and z chose instead of y.
Avatar of 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
With that sample the DISTINCT works. Something still missing.
Avatar of 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)
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
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of Sh M

ASKER

thanks all.
We had to modify the database and additional tables.
Avatar of Sh M

ASKER

Thanks