SQL Join / union question [beginer]

Question

I have two tables, shown below, with the following columns:

[table #Completed] Client_ID, ClientName, FirstDeliveryStatus, FirstDeliveryDate

[table #Planned] Client_ID, ClientName, NextDeliveryStatus, NextDeliveryDate

As you can see clients have some completed & planned deliveries. I'm trying to merge both tables to show for each patient :

Client_ID, ClientName, FirstDeliveryStatus, FirstDeliveryDate, NextDeliveryStatus, NextDeliveryDate

I have tried to select data from table Completed and outer/inner join table Planned but it does not realy work for me. Main issue is row 6[Ashton] who have never had any completed deliveries & once I join details about his planned delivery - i'm getting nulls on Client_ID & ClientName

I would expect to see output as per below visualization

Any help would be appreciated

Answer 1

You have to first decide: Which table do I want to return all records for?

The answer for you sounds like "Client". So start with your client table:

SELECT ClientID, ClientName
FROM Client

Then ask: Which tables do I want to add?

The answer for you sounds like "Completed" and "Planned". So join them to Client:

SELECT c.ClientID, c.ClientName
FROM Client AS c
JOIN Completed AS comp ON c.ClientID = comp.ClientID
JOIN Planned AS p ON c.ClientID = p.ClientID

Now ask: Do I want to return all fields from my Client table, or only those that have matching records in both tables?

The answer for you appears to be: "All records from client and any matching from other tables". So for this, outer join to the tables from your base and return the columns you want:

SELECT c.ClientID, c.ClientName, comp.FirstDeliveryStatus, comp.FirstDeliveryDate, p.NextDeliveryStatus, p.NextDeliveryDate
FROM Client AS c
LEFT OUTER JOIN Completed AS comp ON c.ClientID = comp.ClientID
LEFT OUTER JOIN Planned AS p ON c.ClientID = p.ClientID

Now if you only wish to have records which have a record in Completed or Planned tables, add in a filter:

SELECT c.ClientID, c.ClientName, comp.FirstDeliveryStatus, comp.FirstDeliveryDate, p.NextDeliveryStatus, p.NextDeliveryDate
FROM Client AS c
LEFT OUTER JOIN Completed AS comp ON c.ClientID = comp.ClientID
LEFT OUTER JOIN Planned AS p ON c.ClientID = p.ClientID
WHERE p.ClientID IS NOT NULL OR comp.ClientID IS NOT NULL