Solved

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

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query in Oracle forms Builder 2 49
Extract the first word (before the , ) 2 48
Oracle SQL syntax check  without executing 6 50
grant user/role question 11 25
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

813 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

16 Experts available now in Live!

Get 1:1 Help Now