Join an Additional View to return all combinations

I have the below query (in snippet) which returns all dates regardless of whether there's a matching entry in the other view.  It returns data that looks like this:

Date                  Contact_ID           Order_No
11/1/08              NULL                    NULL
11/2/08              103                       1001
11/3/08              NULL                    NULL
11/4/08              NULL                    NULL
11/5/08              101                      1002

What I want to do now is add a join to another view that would bring in ALL contact_id's for EVERY date - such that the final result would be that I would see every date AND on each of those dates I would see a row for every possible contact_id, regardless of whether there's a match on order_no.  Data which would look like this:

Date                  Contact_ID           Order_No
11/1/08              101                     NULL
11/1/08              102                      NULL
11/1/08              103                     NULL
11/2/08              101                     NULL
11/2/08              102                      NULL
11/2/08              103                     1001
11/3/08              101                     NULL
11/3/08              102                      NULL
11/3/08              103                     NULL
11/4/08              101                     NULL
11/4/08              102                      NULL
11/4/08              103                     NULL
11/5/08              101                     1002
11/5/08              102                      NULL
11/5/08              103                     NULL

The join is going to be on:  dbo.p21_view_oe_hdr.contact_id = dbo.p21_view_contacts.id (the latter being the new view).

I just don't know how to do the joins.

Thanks.
SELECT     TOP 100 PERCENT dbo.TSIDateLookup.DateFull, dbo.p21_view_oe_hdr.contact_id, dbo.p21_view_oe_hdr.order_no
FROM         dbo.p21_view_oe_hdr RIGHT OUTER JOIN
                      dbo.TSIDateLookup ON dbo.p21_view_oe_hdr.order_date = dbo.TSIDateLookup.DateFull
WHERE     (dbo.TSIDateLookup.DateFull >= CONVERT(DATETIME, '2008-11-01 00:00:00', 102))
ORDER BY dbo.TSIDateLookup.DateFull, dbo.p21_view_oe_hdr.contact_id

Open in new window

timoteogaAsked:
Who is Participating?
 
Terry WoodsConnect With a Mentor IT GuruCommented:
Hi again, sorry for the slow response, but it wasn't an easy one. I created a table to test this in MS SQL Server and the below solution seems to work for me (there's a slight chance I've made a syntax error due to my slightly different testing conditions).


SELECT cartesian_prod.DateFull,
       cartesian_prod.contact_id,
       p21_view_oe_hdr.order_no
FROM (select distinct TSIDateLookup.DateFull, p21_view_oe_hdr.contact_id
        from TSIDateLookup, p21_view_oe_hdr
        where p21_view_oe_hdr.contact_id is not null
          and TSIDateLookup.DateFull >= CONVERT(DATETIME, '2008-11-01 00:00:00', 102)
     ) cartesian_prod
     LEFT OUTER JOIN p21_view_oe_hdr on p21_view_oe_hdr.order_date = cartesian_prod.DateFull
                                     and p21_view_oe_hdr.contact_id = cartesian_prod.contact_id
ORDER BY cartesian_prod.DateFull, cartesian_prod.contact_id

Open in new window

0
 
Terry WoodsIT GuruCommented:
Try this:
SELECT TOP 100 PERCENT dbo.TSIDateLookup.DateFull, 
                       cartesian_prod.contact_id,
                       dbo.p21_view_oe_hdr.order_no
FROM dbo.TSIDateLookup
     INNER JOIN dbo.p21_view_oe_hdr cartesian_prod
     LEFT OUTER JOIN dbo.p21_view_oe_hdr
                     ON dbo.p21_view_oe_hdr.order_date = dbo.TSIDateLookup.DateFull
                        AND dbo.p21_view_oe_hdr.contact_id = cartesian_prod.contact_id
WHERE     (dbo.TSIDateLookup.DateFull >= CONVERT(DATETIME, '2008-11-01 00:00:00', 102))
ORDER BY dbo.TSIDateLookup.DateFull, dbo.p21_view_oe_hdr.contact_id

Open in new window

0
 
Terry WoodsIT GuruCommented:
I have switched the right join to a left simply because that's what I'm used to working with... it probably could've been done in a very similar way with a right join...
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Terry WoodsIT GuruCommented:
Actually, I've just realised that's going to return too many records... give me a sec...
0
 
Terry WoodsIT GuruCommented:
This time:
SELECT TOP 100 PERCENT dbo.TSIDateLookup.DateFull, 
                       cartesian_prod.contact_id,
                       dbo.p21_view_oe_hdr.order_no
FROM dbo.TSIDateLookup
     INNER JOIN (select distinct contact_id from dbo.p21_view_oe_hdr) as cartesian_prod
     LEFT OUTER JOIN dbo.p21_view_oe_hdr
                     ON dbo.p21_view_oe_hdr.order_date = dbo.TSIDateLookup.DateFull
                        AND dbo.p21_view_oe_hdr.contact_id = cartesian_prod.contact_id
WHERE     (dbo.TSIDateLookup.DateFull >= CONVERT(DATETIME, '2008-11-01 00:00:00', 102))
ORDER BY dbo.TSIDateLookup.DateFull, dbo.p21_view_oe_hdr.contact_id

Open in new window

0
 
jamesguCommented:
timoteoga, i don't see 102 for the column Contact_ID in your original input. is it supposed to be one?
0
 
timoteogaAuthor Commented:
TerryAtOpus:

When I run either one of those you provided, I get:

Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'WHERE'.
0
 
timoteogaAuthor Commented:
jamesqu:

Contact_id 102 does exist, it just didn't have any sales in that period 11/1 - 11/5, and that's why it didn't appear in that original result set.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.