t-sql outer join across three tables

I have three tables:

CREATE TABLE person
    (id int,
    name char(50))

CREATE TABLE eventtype
    (id int,
     description char(50))

CREATE TABLE event
    (person_id int,
     eventtype_id int,
     duration int)

What I want is a single query which gives me a list of the total duration of each eventtype for each person, including all zero entries. E.g. if there are 10 people and 15 different eventtypes, there should be 150 rows returned, irrespective of the contents of the event table.

I can get an outer join to work between two tables (e.g. durati开发者_Go百科ons for all eventtypes), but not with a second outer join.

Thanks!

You'll have to add a CROSS APPLY to the mix to get the non-existing relations.

SELECT  q.name, q.description, SUM(q.Duration)
FROM    (
          SELECT  p.Name, et.description, Duration = 0
          FROM    person p
                  CROSS APPLY eventtype et
          UNION ALL        
          SELECT  p.Name, et.description, e.duration
          FROM    person p
                  INNER JOIN event e ON e.person_id = p.id
                  INNER JOIN eventtype et ON et.id = e.eventtypeid        
        ) q
GROUP BY
        q.Name, q.description        

You can cross join person and eventtype, and then just join the result to the event table:

SELECT
   p.Name,
   et.Description,
   COALESCE(e.duration,0)
FROM
   person p
      cross join
   eventtype et
      left join
   event e
      on
         p.id = e.person_id and
         et.id = e.eventtype_id

A cross join is one where, for each row in the left table, it's joined to every row in the right table.

If you want a row for every combination of person and eventtype, that suggets a CROSS JOIN. To get the duration we need to join to event, but this needs to be an OUTER join since there might not always be a row. Your use of "total" suggests there there could be more than one event for a given combination of person and event, so we'll need a SUM in there as well.

Sample data:

insert person values ( 1, 'Joe' )
insert person values ( 2, 'Bob' )
insert person values ( 3, 'Tim' )

insert eventtype values ( 1, 'Cake' )
insert eventtype values ( 2, 'Pie' )
insert eventtype values ( 3, 'Beer' )

insert event values ( 1, 1, 10 ) 
insert event values ( 1, 2, 10 ) 
insert event values ( 1, 2, 5 ) 
insert event values ( 2, 1, 10 ) 
insert event values ( 2, 2, 7 ) 
insert event values ( 3, 2, 8 ) 
insert event values ( 3, 3, 16 ) 
insert event values ( 1, 1, 10 ) 

The query:

SELECT
    PET.person_id
    , PET.person_name
    , PET.eventtype_id
    , PET.eventtype_description
    , ISNULL(SUM(E.duration), 0) total_duration
FROM
    (
    SELECT
        P.id person_id
        , P.name person_name
        , ET.id eventtype_id
        , ET.description eventtype_description
    FROM
        person P
        CROSS JOIN eventtype ET
    ) PET
    LEFT JOIN event E ON PET.person_id = E.person_id
                     AND PET.eventtype_id = E.eventtype_id
GROUP BY
    PET.person_id
    , PET.person_name
    , PET.eventtype_id
    , PET.eventtype_description

Output:

person_id   person_name eventtype_id eventtype_description total_duration
----------- ----------- ------------ --------------------- --------------
1           Joe         1            Cake                  20
1           Joe         2            Pie                   15
1           Joe         3            Beer                  0
2           Bob         1            Cake                  10
2           Bob         2            Pie                   7
2           Bob         3            Beer                  0
3           Tim         1            Cake                  0
3           Tim         2            Pie                   8
3           Tim         3            Beer                  16
Warning: Null value is eliminated by an aggregate or other SET operation.

(9 row(s) affected)