CODEWITHSUNDEEP
sql-serversql-server-2008full-outer-join
mulllhausen
mulllhausen

Reputation: 4435

Full outer join on 3 tables

I'm trying to get a full result set of all combinations from 3 tables, but its not working because I can't figure out how to tell SQL-Server 2008 what I want.

I have simplified it down to the following similar problem... I have 3 tables:

table `date_ranges`:
  --------------------------------------------------------
| start_day                  | end_day                     |
| -------------------------------------------------------- |
| November, 01 2015 14:37:00 | November, 02 2015 00:00:00  |
| November, 02 2015 00:00:00 | November, 03 2015 00:00:00  |
| November, 03 2015 00:00:00 | November, 04 2015 00:00:00  |
| November, 04 2015 00:00:00 | November, 04 2015 02:00:00  |
  --------------------------------------------------------

table `sites`:
  ----
| site |
| ---- |
| 1    |
| 2    |
| 3    |
| 4    |
  ---- 

table `all_data`:
  --------------------------------------
| data_date                  | data_site |
| ---------------------------|---------- |
| November, 02 2015 15:35:00 | 1         |
  --------------------------------------

and I want to get the following result:

  -------------------------------------------------------------------------------------------------------
| data_date                  | data_site | start_day                  | end_day                    | site |
| ------------------------------------------------------------------------------------------------------- |
| null                       | null      | November, 01 2015 14:37:00 | November, 02 2015 00:00:00 | 1    |
| November, 02 2015 15:35:00 | 1         | November, 02 2015 00:00:00 | November, 03 2015 00:00:00 | 1    |
| null                       | null      | November, 03 2015 00:00:00 | November, 04 2015 00:00:00 | 1    |
| null                       | null      | November, 04 2015 00:00:00 | November, 04 2015 02:00:00 | 1    |
| null                       | null      | November, 01 2015 14:37:00 | November, 02 2015 00:00:00 | 2    |
| null                       | null      | November, 02 2015 00:00:00 | November, 03 2015 00:00:00 | 2    |
| null                       | null      | November, 03 2015 00:00:00 | November, 04 2015 00:00:00 | 2    |
| null                       | null      | November, 04 2015 00:00:00 | November, 04 2015 02:00:00 | 2    |
| null                       | null      | November, 01 2015 14:37:00 | November, 02 2015 00:00:00 | 3    |
| null                       | null      | November, 02 2015 00:00:00 | November, 03 2015 00:00:00 | 3    |
| null                       | null      | November, 03 2015 00:00:00 | November, 04 2015 00:00:00 | 3    |
| null                       | null      | November, 04 2015 00:00:00 | November, 04 2015 02:00:00 | 3    |
| null                       | null      | November, 01 2015 14:37:00 | November, 02 2015 00:00:00 | 4    |
| null                       | null      | November, 02 2015 00:00:00 | November, 03 2015 00:00:00 | 4    |
| null                       | null      | November, 03 2015 00:00:00 | November, 04 2015 00:00:00 | 4    |
| null                       | null      | November, 04 2015 00:00:00 | November, 04 2015 02:00:00 | 4    |
  -------------------------------------------------------------------------------------------------------

But instead I can only figure out how to get the following result (see fiddle here):

  -------------------------------------------------------------------------------------------------------
| data_date                  | data_site | start_day                  | end_day                    | site |
| ------------------------------------------------------------------------------------------------------- |
| null                       | null      | November, 01 2015 14:37:00 | November, 02 2015 00:00:00 | null |
| November, 02 2015 15:35:00 | 1         | November, 02 2015 00:00:00 | November, 03 2015 00:00:00 | 1    |
| null                       | null      | November, 03 2015 00:00:00 | November, 04 2015 00:00:00 | null |
| null                       | null      | November, 04 2015 00:00:00 | November, 04 2015 02:00:00 | null |
| null                       | null      | null                       | null                       | 2    |
| null                       | null      | null                       | null                       | 3    |
| null                       | null      | null                       | null                       | 4    |
  -------------------------------------------------------------------------------------------------------

using the following incorrect query:

select * from all_data d
full outer join date_ranges r on (r.start_day <= d.data_date and d.data_date < r.end_day)
full outer join sites s on s.site = d.data_site

Upvotes: 1

Views: 1195

Answers (1)

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

A combination of CROSS JOIN and LEFT JOIN should do the trick for you.

Something like this..

SQL Fiddle

SELECT * 
FROM DATE_RANGES R
    CROSS JOIN SITES S 
    LEFT OUTER JOIN ALL_DATA D ON (R.START_DAY <= D.DATA_DATE AND D.DATA_DATE < R.END_DAY)
                                        AND S.SITE = D.DATA_SITE

Upvotes: 5

Related Questions