0

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

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

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

enter image description here

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

enter image description here

Any help would be appreciated

  • It would help if you showed us the exact query used to produce that table with the combined data of the other two. – FrustratedWithFormsDesigner Feb 21 '17 at 19:21
  • 1
    @FrustratedWithFormsDesigner - The combined table is stated to be a visualization - presumably built manually. If OP had a query that returned that combined data, then he probably wouldn't be asking how to create that query. – RDFozz Feb 21 '17 at 22:42
0

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
  • 1
    What about a FULL OUTER JOIN? – FrustratedWithFormsDesigner Feb 21 '17 at 20:08
  • 1
    Sure, whatever floats your boat :-) – blobbles Feb 21 '17 at 20:26
  • A FULL OUTER JOIN between #Completed and #Planned would work very well, if there is no Client table. Note: there really needs to be a Client table, if for no other reason than to ensure ClientID remains a unique identifier. On the other hand, if there's a chance that there's a ClientID in either of the other two tables that's not in the Client table, you'd be back to getting NULL ClientID and ClientName. – RDFozz Feb 21 '17 at 22:54
  • NOTE: if using a FULL OUTER JOIN between #Completed and #Planned, without a Client table, the first two items in the SELECT list would be COALESCE(comp.ClientID, p.ClientID) as ClientID, COALESCE(comp.ClientName, p.ClientName) as ClientName (or, if you prefer, use ISNULL instead of COALESCE; results would be the same). – RDFozz Feb 21 '17 at 22:57
  • Agree - I have assumed there is a client table (i.e. the database is designed to 3NF). If there is not... well... it will be very difficult to get queries that produce the right results. The FULL OUTER as you suggest would only work for the currently visible example data. (for example what if there are different ID-Name matches in both tables). – blobbles Feb 21 '17 at 23:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.