SQL Join / union question [beginer]

join;

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

Best Answer

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

Related Solutions

Sql-server – How to use merge hints to isolate complex queries in SQL Server

If you use a multi-statement UDF, then your inner select is executed exactly once for each outer row. The multi-statement UDF is treated as a black box: the execution plan will now show access to the objects used in your complex view.

On the other hand, a subquery and/or an inline UDF is flattened out by the optimizer. When this is the case, the execution plan will include access to the objects used in your complex view.

Sql-server – SQL join query to show rows with non-existent rows in one table

Thank you for SQLfiddle and sample data! I wish more questions started this way.

If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join to an inner join, because it will exclude any rows that are NULL on that side (because it doesn't know if NULL would match the filter or not).

I modified the first query to get a row for every member:

SELECT Members.Member_ID
      ,Time_Entry.Date_Start
      ,Time_Entry.Hours_Actual
      ,Time_Entry.Hours_Bill
FROM dbo.Members
  LEFT OUTER JOIN dbo.Time_Entry
--^^^^ changed from FULL to LEFT
  ON Members.Member_ID = Time_Entry.Member_ID
  AND Time_Entry.Date_Start = '20131110';
--^^^ changed from WHERE to AND

I'll leave it as an exercise for the reader to take it from there and add the other columns, formatting, COALESCE etc.

Some other notes:

please always use the schema prefix when creating and referencing objects
please always use a length when converting to varchar etc.
stay away from ambiguous, regional date formats like mm-dd-yyyy

consider using aliases to make your queries easier to read. E.g. the above could be re-written as:

SELECT m.Member_ID
  ,t.Date_Start
  ,t.Hours_Actual
  ,t.Hours_Bill
FROM dbo.Members AS m
LEFT OUTER JOIN dbo.Time_Entry AS t
ON m.Member_ID = t.Member_ID
AND t.Date_Start = '20131110';

... a lot tidier, IMHO, as long as you use sensible aliases.

Best Answer

Related Solutions

Sql-server – How to use merge hints to isolate complex queries in SQL Server

Sql-server – SQL join query to show rows with non-existent rows in one table

Related Question