Solved

sql join -

Posted on 2014-11-25
23
134 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
  • 12
  • 6
  • 4
  • +1
23 Comments
 
LVL 9

Expert Comment

by:teebon
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:shmz
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Suggest to have your Name column in Table 2 or Table 4.
0
 

Author Comment

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

Expert Comment

by:teebon
Comment Utility
Hi shmz,

Please share example data for each of the tables so that further advice can be provided.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
Comment Utility
Actually, doesn't matter if I use the left join or not, returns same number of records.
0
 

Author Comment

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

Expert Comment

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

Author Comment

by:shmz
Comment Utility
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 45

Expert Comment

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

Author Comment

by:shmz
Comment Utility
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 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
thanks all.
We had to modify the database and additional tables.
0
 

Author Closing Comment

by:shmz
Comment Utility
Thanks
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now