March 21, 2017 at 5:19 pm
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
March 22, 2017 at 1:31 am
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
March 22, 2017 at 6:57 am
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;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply