Difficult query in either Linq To Sql or SQL

linq-to-sql, sql

Solution

You should try a `CROSS JOIN` on the two reference tables, and then `OUTER JOIN` back to your 'data' table - and then check whether the `Observation` is null... then group and sum!

SELECT 
    [Name],
    [Date],
    SUM([Exists])
FROM
(
SELECT 
    name, 
    [Date],
    CASE WHEN o.Id IS NULL THEN 0
        ELSE 1
    END as [Exists]
FROM 
    Rating r CROSS JOIN
    Inspection i
    LEFT OUTER JOIN Observation o 
        ON o.RatingId = r.Id AND o.InspectionId = i.Id
) as [source]
GROUP BY [Name], [Date]
ORDER BY
    [Date],
    name

Translating to LINQ would be a similar two-step process - get the inner result set (checking whether observation is NULL), before grouping and summing.

Problem

I've been working on this for 2 days and I can't figure it out. I'm hoping someone smarter than me will give this a go. Let's say I have the following tables: ``` Rating: Id | Name 1 | A 2 | B 3 | C 4 | D 5 | E Inspection: Id | Date (mm/dd/yyyy) 1 | 01/04/2012 2 | 04/04/2012 3 | 28/03/2012 4 | 04/04/2012 Observation: Id | InspectionId | RatingId 1 | 2 | 3 2 | 1 | 2 3 | 4 | 3 4 | 2 | 1 5 | 3 | 3 6 | 1 | 2 ``` I want the query to return: ``` RatingName | Date(mm/dd/yyyy) | ObservationCount A | 01/04/2012 | 0 B | 01/04/2012 | 1 C | 01/04/2012 | 1 D | 01/04/2012 | 0 E | 01/04/2012 | 0 A | 04/04/2012 | 1 B | 04/04/2012 | 0 C | 04/04/2012 | 2 D | 04/04/2012 | 0 E | 04/04/2012 | 0 A | 28/03/2012 | 0 B | 28/03/2012 | 0 C | 28/03/2012 | 1 D | 28/03/2012 | 0 E | 28/03/2012 | 0 ``` So I need the number of Observations for each rating for each date. And yes I need to have the records which return 0 Observations because I'm using this data in a stacked chart and without them it throws an error. I've managed to get the above table but without the records that return 0 Observations with the following Linq To Sql query, but from this point I get stuck. ``` MyDataContext DB = new MyDataContext(); var data = (from r in DB.Ratings join o in DB.Observations on r.Id equals o.RatingId into ro from observation in ro.DefaultIfEmpty() join i in DB.Inspections on observation.InspectionId equals i.Id into roi from q in roi.DefaultIfEmpty() group q by new { Name = r.Name, Date = q.Date } into grouped select new { RatingName = grouped.Key.Name, Date = grouped.Key.Date, ObservationCount = grouped.Count(x => x != null) }).OrderBy(x => x.Date); ``` I would appreciate an answer in either Linq To Sql or just plain old SQL, thanks!

Original source