Oscar Rodriguez
asked on
SQL: using Left join but need to pull in records that are not meeting left join condition.
Hi,
Table 1 has all the information I need, but I need to pull in some additional data elements from Table 2 so I used a left join based on (On Table 1 GL, CO, Costctr = Table 2 GL, CO , Costctr ), the thing is from table 1 to table 2 there are some instances where the Co# and Costctr changes values... so the on clause (On Table 1 GL, CO, Costctr = Table 2 GL, CO , Costctr) is not picking up those values that are changing in table 2 so my report is incomplete. I need to somehow bring in those records that have changed to show them on my report. below is the ideal output I would like to see based on Table1, Table 2 data
From Table 1
GL Co# Costctr Summary amount
101444 0203 1230323 20,000.00
101444 1234 3698523 30,000.00
Left join Table 2
GL Co# Costctr Report amount
101444 0996 9997546 30,000.00
101444 1234 3698523 20,000.00
On Table 1 GL, CO, Costctr = Table 2 GL, CO , Costctr
Output results:
GL Co# Costctr Summary Report
101444 0203 1230323 - 20,000.00
101444 1234 3698523 50,000.00 -
101444 0996 9997546 - 30,000.00
Table 1 has all the information I need, but I need to pull in some additional data elements from Table 2 so I used a left join based on (On Table 1 GL, CO, Costctr = Table 2 GL, CO , Costctr ), the thing is from table 1 to table 2 there are some instances where the Co# and Costctr changes values... so the on clause (On Table 1 GL, CO, Costctr = Table 2 GL, CO , Costctr) is not picking up those values that are changing in table 2 so my report is incomplete. I need to somehow bring in those records that have changed to show them on my report. below is the ideal output I would like to see based on Table1, Table 2 data
From Table 1
GL Co# Costctr Summary amount
101444 0203 1230323 20,000.00
101444 1234 3698523 30,000.00
Left join Table 2
GL Co# Costctr Report amount
101444 0996 9997546 30,000.00
101444 1234 3698523 20,000.00
On Table 1 GL, CO, Costctr = Table 2 GL, CO , Costctr
Output results:
GL Co# Costctr Summary Report
101444 0203 1230323 - 20,000.00
101444 1234 3698523 50,000.00 -
101444 0996 9997546 - 30,000.00
What's wrong with a UNION??
DECLARE @T1 TABLE
(
GL INT ,
Co# INT ,
Costctr INT ,
amount FLOAT
);
DECLARE @T2 TABLE
(
GL INT ,
Co# INT ,
Costctr INT ,
amount FLOAT
);
INSERT INTO @T1
VALUES ( 101444, 0203, 1230323, 20000.00 ),
( 101444, 1234, 3698523, 30000.00 );
INSERT INTO @T2
VALUES ( 101444, 0996, 9997546, 30000.00 ),
( 101444, 1234, 3698523, 20000.00 );
WITH Unioned
AS ( SELECT *
FROM @T1
UNION ALL
SELECT *
FROM @T2
)
SELECT U.GL ,
U.Costctr ,
U.Co# ,
SUM(U.amount)
FROM Unioned U
GROUP BY U.GL ,
U.Costctr ,
U.Co#ASKER
Hi, I cant post real query as per company rules, but Left join is working correctly, my problem is how to bring in the information that is changing from table 1 to table 2. We have a process in between table 1 and table 2 where users can reallocate the amount to different Co# and Cost Ctr. So I want to try and get those records where the Co# and Costctr are changing back in my results.
GL Co# Costctr Summary amount
101444 0203 1230323 20,000.00
101444 1234 3698523 30,000.00
Left join Table 2
GL Co# Costctr Report amount
101444 0996 9997546 30,000.00 (changed from table1, and is now this Co# and Cost Ctr and it not being returned with Left join)
101444 1234 3698523 20,000.00
GL Co# Costctr Summary amount
101444 0203 1230323 20,000.00
101444 1234 3698523 30,000.00
Left join Table 2
GL Co# Costctr Report amount
101444 0996 9997546 30,000.00 (changed from table1, and is now this Co# and Cost Ctr and it not being returned with Left join)
101444 1234 3698523 20,000.00
>>Output results<<
Are those the results you are getting now or the results you want to receive?
Are those the results you are getting now or the results you want to receive?
ASKER
For Union, outside the 3 key columns GL, Co#, Costctr, the columns in table 1 are different than table 2 and I would need most columns from table 1 and only a few from table 2.
ASKER
That is what I would like to get... My Ideal results
Then use a FULL join:
SELECT ISNULL(T1.GL, T2.GL) ,
ISNULL(T1.Costctr, T2.Costctr) ,
ISNULL(T1.Co#, T2.Co#) ,
COALESCE(T1.amount + T2.amount, T1.amount, T2.amount)
FROM @T1 T1
FULL OUTER JOIN @T2 T2 ON T1.GL = T2.GL
AND T1.Costctr = T2.Costctr
AND T1.Co# = T2.Co#;ASKER
Full outer join, let me try that...
ASKER
Hi Stefan, for the amounts, they are different amount columns in each table, so on final results they would be represented as t1.amount and t2.amount. so that logic you had would not work, I don't think...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>"I cant post real query as per company rules, "
Change the table names
Remove anything the identifies compnany such as comments. Its no longer the real query.
Could you also make sample data easier to trace. If every dollar amount were different we would know which table it came from.
Change the table names
Remove anything the identifies compnany such as comments. Its no longer the real query.
Could you also make sample data easier to trace. If every dollar amount were different we would know which table it came from.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys, really appreciate the help
I really did not want to take points from Stefan's good work.
I was only attempting to help demonstrate that using full outer join as proposed by Stefan would produce the requested result.
I was only attempting to help demonstrate that using full outer join as proposed by Stefan would produce the requested result.
Based on a first assumption, I would say that if your query is not delivering exactly what you are describing above with a left join, this means that something else could be forcing it to a inner join. This usually happens when you mix up left and inner joins in your query.