[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

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
0
Oscar Rodriguez
Asked:
Oscar Rodriguez
  • 6
  • 3
  • 3
  • +2
2 Solutions
 
Walter RitzelSenior Software EngineerCommented:
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
 
ste5anSenior DeveloperCommented:
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
 
Oscar RodriguezAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
awking00Commented:
>>Output results<<
Are those the results you are getting now or the results you want to receive?
0
 
Oscar RodriguezAuthor Commented:
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
 
Oscar RodriguezAuthor Commented:
That is what I would like to get... My Ideal results
0
 
ste5anSenior DeveloperCommented:
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
 
Oscar RodriguezAuthor Commented:
Full outer join,  let me try that...
0
 
Oscar RodriguezAuthor Commented:
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
 
ste5anSenior DeveloperCommented:
??

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
 
PortletPaulCommented:
>>"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
 
PortletPaulCommented:
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
 
Oscar RodriguezAuthor Commented:
Thanks guys,  really appreciate the help
0
 
PortletPaulCommented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 6
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now