[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Join an Additional View to return all combinations

Posted on 2008-11-06
8
Medium Priority
?
195 Views
Last Modified: 2010-03-20
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

0
Comment
Question by:timoteoga
  • 5
  • 2
8 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22901214
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22901216
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 22901221
Actually, I've just realised that's going to return too many records... give me a sec...
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Terry Woods
ID: 22901228
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
 
LVL 9

Expert Comment

by:jamesgu
ID: 22901556
timoteoga, i don't see 102 for the column Contact_ID in your original input. is it supposed to be one?
0
 

Author Comment

by:timoteoga
ID: 22934293
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
 

Author Comment

by:timoteoga
ID: 22934306
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
 
LVL 35

Accepted Solution

by:
Terry Woods earned 1500 total points
ID: 22972713
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question