Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 147
  • Last Modified:

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
0
shmz
Asked:
shmz
  • 12
  • 6
  • 4
  • +1
1 Solution
 
teebonCommented:
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?
0
 
shmzAuthor Commented:
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.
0
 
shmzAuthor Commented:
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)
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
shmzAuthor Commented:
this is just an example to say what I expect to get from query above if it works correctly.
0
 
teebonCommented:
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?
0
 
shmzAuthor Commented:
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.
0
 
teebonCommented:
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.
0
 
shmzAuthor Commented:
I think so.
I am open to totally change the structure of the query if you know the solution?
0
 
teebonCommented:
Suggest to have your Name column in Table 2 or Table 4.
0
 
shmzAuthor Commented:
I can't change the database!
I can only change the query.
0
 
teebonCommented:
Hi shmz,

Please share example data for each of the tables so that further advice can be provided.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

0
 
shmzAuthor Commented:
Actually, doesn't matter if I use the left join or not, returns same number of records.
0
 
shmzAuthor Commented:
I expect 11 records but it returns 33 records.
Looks like each records is repeated 3 times if that helps.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then you really need to provide the logic why are x and z chose instead of y.
0
 
shmzAuthor Commented:
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
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
With that sample the DISTINCT works. Something still missing.
0
 
shmzAuthor Commented:
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)
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Scott PletcherSenior DBACommented:
Use a subquery instead of a join to do the name lookup:

select distinct tblx.x
   , fo.col3
   ,(select top(1) o.NAME
     from table1 o
     where o.tbl2_ID = tw.tbl2_ID
     /*order by <whatever>*/ ) AS name --add order by if needed to limit multiple matches
 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)
0
 
teebonCommented:
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

0
 
shmzAuthor Commented:
thanks all.
We had to modify the database and additional tables.
0
 
shmzAuthor Commented:
Thanks
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 12
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now