Solved

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

Posted on 2016-11-21
14
64 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 88
Repeat query 13 62
what privileges needed for S2 for this function (Oracle 12c)? 3 23
why truncate is faster than delete in oracle ? 4 28
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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.

821 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