Solved

sql join -

Posted on 2014-11-25
23
141 Views
Last Modified: 2015-04-18
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
Comment
Question by:shmz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 6
  • 4
  • +1
23 Comments
 
LVL 9

Expert Comment

by:teebon
ID: 40465988
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
 

Author Comment

by:shmz
ID: 40466062
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
 

Author Comment

by:shmz
ID: 40466065
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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 

Author Comment

by:shmz
ID: 40466066
this is just an example to say what I expect to get from query above if it works correctly.
0
 
LVL 9

Expert Comment

by:teebon
ID: 40466067
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
 

Author Comment

by:shmz
ID: 40466071
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
 
LVL 9

Expert Comment

by:teebon
ID: 40466076
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
 

Author Comment

by:shmz
ID: 40466407
I think so.
I am open to totally change the structure of the query if you know the solution?
0
 
LVL 9

Expert Comment

by:teebon
ID: 40466411
Suggest to have your Name column in Table 2 or Table 4.
0
 

Author Comment

by:shmz
ID: 40466438
I can't change the database!
I can only change the query.
0
 
LVL 9

Expert Comment

by:teebon
ID: 40466621
Hi shmz,

Please share example data for each of the tables so that further advice can be provided.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40466766
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
 

Author Comment

by:shmz
ID: 40466822
Actually, doesn't matter if I use the left join or not, returns same number of records.
0
 

Author Comment

by:shmz
ID: 40466830
I expect 11 records but it returns 33 records.
Looks like each records is repeated 3 times if that helps.
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40466833
Then you really need to provide the logic why are x and z chose instead of y.
0
 

Author Comment

by:shmz
ID: 40466846
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40466866
With that sample the DISTINCT works. Something still missing.
0
 

Author Comment

by:shmz
ID: 40467068
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40467083
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40468069
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
 
LVL 9

Expert Comment

by:teebon
ID: 40468390
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
 

Author Comment

by:shmz
ID: 40472874
thanks all.
We had to modify the database and additional tables.
0
 

Author Closing Comment

by:shmz
ID: 40731182
Thanks
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question