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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Terry WoodsIT GuruCommented:
Actually, I've just realised that's going to return too many records... give me a sec...
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

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
Terry WoodsIT 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.