Solved

SQL: using Left join but need to pull in records that are not meeting left join condition.

Posted on 2016-11-21
14
72 Views
Last Modified: 2016-11-22
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
0
Comment
Question by:Oscar Rodriguez
[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
  • 6
  • 3
  • 3
  • +2
14 Comments
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 41896406
Can you post your real query?
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.
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41896419
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#

Open in new window


Capture.PNG
0
 

Author Comment

by:Oscar Rodriguez
ID: 41896420
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
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 32

Expert Comment

by:awking00
ID: 41896423
>>Output results<<
Are those the results you are getting now or the results you want to receive?
0
 

Author Comment

by:Oscar Rodriguez
ID: 41896425
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.
0
 

Author Comment

by:Oscar Rodriguez
ID: 41896427
That is what I would like to get... My Ideal results
0
 
LVL 33

Expert Comment

by:ste5an
ID: 41896443
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#;

Open in new window

0
 

Author Comment

by:Oscar Rodriguez
ID: 41896447
Full outer join,  let me try that...
0
 

Author Comment

by:Oscar Rodriguez
ID: 41896481
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...
0
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 225 total points
ID: 41896502
??

SELECT  ISNULL(T1.GL, T2.GL) ,
        ISNULL(T1.Costctr, T2.Costctr) ,
        ISNULL(T1.Co#, T2.Co#) ,
        T1.amount + T2.amount, 
		ISNULL(IIF(T1.amount + T2.amount IS NULL, T1.amount, NULL), 
		IIF(T1.amount + T2.amount IS NULL, T2.amount, NULL))
FROM    @T1 T1
        FULL OUTER JOIN @T2 T2 ON T1.GL = T2.GL
                                  AND T1.Costctr = T2.Costctr
                                  AND T1.Co# = T2.Co#;

Open in new window

1
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41896589
>>"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.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 275 total points
ID: 41896852
no points please
query from https://www.experts-exchange.com/questions/28984538/SQL-using-Left-join-but-need-to-pull-in-records-that-are-not-meeting-left-join-condition.html#a41896502

CREATE TABLE Table1
    ([GL] int, [Co] int, [Costctr] int, [amount] money)
;
    
INSERT INTO Table1
    ([GL], [Co], [Costctr], [amount])
VALUES
    (101444, 0203, 1230323, 20000.00),
    (101444, 1234, 3698523, 30000.00)
;


CREATE TABLE Table2
    ([GL] int, [Co] int, [Costctr] int, [amount] money)
;
    
INSERT INTO Table2
    ([GL], [Co], [Costctr], [amount])
VALUES
    (101444, 0996, 9997546, 30000.00),
    (101444, 1234, 3698523, 20000.00)
;

SELECT  ISNULL(T1.GL, T2.GL) ,
        ISNULL(T1.Costctr, T2.Costctr) ,
        ISNULL(T1.Co, T2.Co) ,
        T1.amount + T2.amount, 
		ISNULL(IIF(T1.amount + T2.amount IS NULL, T1.amount, NULL), 
		IIF(T1.amount + T2.amount IS NULL, T2.amount, NULL))
FROM    table1 T1
        FULL OUTER JOIN table2 T2 ON T1.GL = T2.GL
                                  AND T1.Costctr = T2.Costctr
                                  AND T1.Co = T2.Co;

Open in new window

+---+------------------+------------------+------------------+------------------+------------------+
|   | (No column name) | (No column name) | (No column name) | (No column name) | (No column name) |
+---+------------------+------------------+------------------+------------------+------------------+
| 1 |           101444 |          1230323 |              203 | NULL             | 20000.00         |
| 2 |           101444 |          3698523 |             1234 | 50000.00         | NULL             |
| 3 |           101444 |          9997546 |              996 | NULL             | 30000.00         |
+---+------------------+------------------+------------------+------------------+------------------+

Open in new window

0
 

Author Closing Comment

by:Oscar Rodriguez
ID: 41898494
Thanks guys,  really appreciate the help
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41898514
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup

740 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