Left join a table on itself - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Left join a table on itself

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-29 : 19:13:23

It seems to me -- I'm just a beginner, though -- that trying to perform a left join here is incorrect in selecting a count of IDs, because the IDs of Table2 are already included in the inner joined alias A. Or is this legitimate?

SELECT T1.ID, B.ID_Total_Count
FROM Table1 AS T1
INNER JOIN (
SELECT DISTINCT ID
FROM Table2
UNION
SELECT DISTINCT ID
FROM Table3
) AS A
ON A.ID = T1.ID
LEFT JOIN Table2 AS B ON B.ID = A.ID
WHERE T1.ID IN (
SELECT DISTINCT ID
FROM Table4
)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-30 : 01:28:36

you dont require DISTINCT inside subquery as UNION already takes distinct. cant understand meaning of this query though. can you state what you're trying to do here

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dmilam
Posting Yak Master

185 Posts

Posted - 2010-04-30 : 13:59:47

The code is an attempt at reducing a much longer real-world query, trying to show customer transaction history.

1. Separate customer transaction history into monthly reports. Customer transaction history is not derived from the main customer database, but is separate.
2. Select IDs from the main customer database, and their monthly transaction counts (based on the monthly report tables). Why, because the main customer database is the only one to provide current names. Customers may have had transactions in the past, but they have been potentially purged since. We need not only IDs, but names too.
3. Thus, inner join these IDs to all monthly reports rolled into one (hence the UNION clauses), removing any past customers who no longer are active in the main database.
4. Then left join the monthly reports to this rolled up customer base, to show what (if any, if not, NULL) transactions the current named customers had for those months.

Perhaps I am in doubt because I am just confused

, because as you can see, it's a bit complicated. But still, it seems 'circular' to me somehow.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-01 : 00:47:04

I didnt get step 4. isnt inner join itself removes those customers which are not active (i.e doesnt have any records in monthly tables). then why the last left join is necessary?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dmilam
Posting Yak Master

185 Posts

Posted - 2010-05-05 : 14:35:48

To show NULL values for any table already in the INNER JOIN segement. Here is a fuller accounting:

SELECT p.ID, 
      p.Name, 
      p.Transaction1, 
      b.[Q1 2010 Transaction Count],   
      c.[Q4 2009 Transaction Count],   
      d.[Q3 2009 Transaction Count],  
      e.[Q2 2009 Transaction Count],  
      f.[Q1 2009 Transaction Count],  
      g.[Q4 2008 Transaction Count]        
FROM d1.dbo.People p 
      INNER JOIN (SELECT DISTINCT IDN 
                        FROM t..[Q1 2010] 

                        UNION 
                        
                        SELECT DISTINCT IDN 
                        FROM t..[Q4 2009] 

                        UNION 
                        
                        SELECT DISTINCT IDN 
                        FROM t..[Q3 2009] 

                        UNION 
                        
                        SELECT DISTINCT IDN 
                        FROM t..[Q2 2009]  

                        UNION 
                        
                        SELECT DISTINCT IDN 
                        FROM t..[Q1 2009] 

                        UNION 
                        
                        SELECT DISTINCT IDN 
                        FROM t..[Q4 2008]) as a ON a.IDN = p.ID 
      LEFT JOIN t..[Q1 2010] b on b.IDN = a.IDN 
      LEFT JOIN t..[Q4 2009] c on c.IDN = a.IDN 
      LEFT JOIN t..[Q3 2009] d on d.IDN = a.IDN 
      LEFT JOIN t..[Q2 2009] e on e.IDN = a.IDN 
      LEFT JOIN t..[Q1 2009] f on f.IDN = a.IDN 
      LEFT JOIN t..[Q4 2008] g on g.IDN = a.IDN 
WHERE p.ID in (SELECT DISTINCT IDN 
                        FROM t..[50 or more Transactions 2009])

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 14:40:05

ok i got now

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources