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

I am trying to get some reporting done for employee time records.

We have two tables specifically for this question. Employees are listed in the Members table and each day they enter time entries of work they've performed and is stored in the Time_Entry table.

Example setup with SQL Fiddle: http://sqlfiddle.com/#!3/e3806/7

The end result I'm going for is a table which shows ALL the Members in a column list and then will show their sum hours for the date queried in the other columns.

The problem seems to be that if there is no row in the Time_Entry table for a particular member, there is now row for that member. I've tried several different join types (Left, Right, Inner, Outer, Full Outer, etc.) but none seem to give me what I want, which would be (based on the last example in SQL Fiddle):

/*** Desired End Result ***/

Member_ID   | COUNTTime_Entry | TIMEENTRYDATE | SUMHOURS_ACTUAL | SUMHOURS_BILL
ADavis      | 0               | 11-10-2013    | 0               | 0
BTronton    | 0               | 11-10-2013    | 0               | 0
CJones      | 0               | 11-10-2013    | 0               | 0
DSmith      | 0               | 11-10-2013    | 0               | 0
EGirsch     | 1               | 11-10-2013    | 0.92            | 1
FRowden     | 0               | 11-10-2013    | 0               | 0

What I'm currently getting when I query for specific date of 11-1:

Member_ID   | COUNTTime_Entry | TIMEENTRYDATE | SUMHOURS_ACTUAL | SUMHOURS_BILL
EGirsch     | 1               | 11-10-2013    | 0.92            | 1

Which is correct based on the one Time Entry row that is dated 11-10-2013 for EGirsch, but I need to see zeros for the other members in order to get reports and eventually a web dashboard/report for this information.

This is my first question, and while I searched for Join queries, etc. I'm honestly not sure what this function might be called, so I hope that this isn't a duplicate and will help others too trying to find a solutio

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.

StackExchange Database Administrators Q#53151, answer score: 12