Solved

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

Posted on 2016-11-21
14
49 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 15

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
 
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
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.

 

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 Database Upgrade 13 62
PL/SQL - Leading zeros 7 60
Error executing command from server 6 42
scheduler for Procedure in DB with 3 arguments in 10g 7 29
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…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now