Join Tables

• georgeg 69122

  Old Hand

  Points: 312

  More actions

  March 21, 2017 at 5:19 pm

  #401821

  I have two tables
  Table 1 Customer Activity Table

  
  Table 2 Destination Table
   

  I need to join these two tables into 1 table I need all of the records from table 1, and only those records from table 2 where the customer number and the destination number match.
  I have tried using a left outer join, but it is not working. the joined table should have as many rows as table 1.
  any help would be appreciated.
  Georgeg

• drew.allen

  SSC Guru

  Points: 76944

  More actions

  March 22, 2017 at 1:31 am

  #1934253

  A LEFT OUTER JOIN should have worked.  Can you post your query and we can figure out what you did wrong?

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Ken McKelvey

  SSCoach

  Points: 18766

  More actions

  March 22, 2017 at 6:57 am

  #1934287

  You probably need to ensure that all the filters on the outer table are in the JOIN and not the WHERE clause.
  Logically the FROM clause, with JOINS, is processed before the WHERE clause. This means outer table filters in the WHERE clause will convert a LEFT JOIN into an INNER JOIN unless you use OR NULL type syntax.
  This should work:
  
SELECT *
FROM Table1 T1
    LEFT JOIN Table2 T2
        ON T1.Customer_no = T2.Customer_no
            AND T1.Destination_no = T2.Destination_no;

  This is in effect an INNER JOIN:
  
SELECT *
FROM Table1 T1
    LEFT JOIN Table2 T2
        ON T1.Customer_no = T2.Customer_no
WHERE T1.Destination_no = T2.Destination_no;

  This will also work but is a bit convoluted:
  
SELECT *
FROM Table1 T1
    LEFT JOIN Table2 T2
        ON T1.Customer_no = T2.Customer_no
WHERE T1.Destination_no = T2.Destination_no
    OR T2.Destination_no IS NULL;