Title: SQL Join and union All
Tags: sql;mysqli
Question: I am trying to join 3 tables in SQL.
Table1
CustomerID   FirstName   LastName    Address       EmailAddress
1             bob               S             1111        monica79@example.org
2             jim               B             2222        monica79@example.org
3             hank              H             333         monica79@example.org
Table2
CustomerID     Date    MemberID
1           5/15/86  1
2           5/16/86  2
Table3
CustomerID     Lengthofstay
1               5
2              16
As you can see I have 3 customer in Table 1. I need to join all 3 table. But in table 2 and 3 I need it to be NULL where customer 3 doesn't have any info for table 2 and 3. It would still have all of customers 3 info that is table 1 but the blank info that isn't there for customer 3 would be NULL.
I have Tried this but it leaves out customer 3:
SELECT
A.CustomerID,
A.Firstname,
A.Lastname,
A.Address,
A.EmailAddress,
B.CustomerID,
B.Date,
B.memberID,
C.CustomerID,
C.LengthofStay
FROM TABLE1 as A
JOIN Table2 as B on ( A.CustomerID = B.CustomerID)
LEFT JOIN TABLE3 as C on ( A.CustomerId = C.CustomerID)
This leaves out customer 3. I have tried using:
WHERE A.CustomerID IS NULL; but I get nothing back. Thanks

Here is the accepted answer: OK, your nearly there, you need to convert the first join to a LEFT JOIN:
SELECT
A.CustomerID,
A.Firstname,
A.Lastname,
A.Address,
A.EmailAddress,
B.CustomerID,
B.Date,
B.memberID,
C.CustomerID,
C.LengthofStay
FROM TABLE1 as A
LEFT JOIN Table2 as B on ( A.CustomerID = B.CustomerID)
LEFT JOIN TABLE3 as C on ( A.CustomerId = C.CustomerID)
Comment for this answer: Yes the values returned from TableB & TableC for customer 3 will be null. see http://sqlfiddle.com/#!3/237ce4/1/0
Comment for this answer: will this provide the NULL value for customer 3 in the table 2 and 3 values? Thanks


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:06/09/13', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<sql><mysqli>', 'Title': 'SQL Join and union All', 'ViewCount': 120, 'id': 17014508, 'user_AboutMe': '', 'user_AccountId': 1015849.0, 'user_DisplayName': 'Trying_hard', 'user_Reputation': 8303.0, 'user_Views': 467.0, 'user_id': 1027427.0}}