Solved

SQL Query per requirement

Posted on 2015-02-21
10
96 Views
Last Modified: 2015-02-21
Requirement: Write a query that returns all customers, but match orders only if they were placed in February 2008.

Question: Which of the following queries meets the above requirement and why one you choose but not the other?

Query 1 (110 records):
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT JOIN Sales.Orders AS O
    WHERE C.custid = O.custid
AND o.orderdate >='20080101' AND o.orderdate <'20080301'

Open in new window

Query 2  (143 records):
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT JOIN Sales.Orders AS O
    ON C.custid = O.custid
AND o.orderdate >='20080101' AND o.orderdate <'20080301'

Open in new window


The book says that: Because both the comparison between the customer’s customer ID and the order’s customer ID, and the date range are considered part of the matching logic, both should appear in the ON clause, as follows.

I think this is not correct; we need to filter dates in WHERE clause but of course have custId with ON (the way Query 1 has them). What do you think?

Not schoolwork. EXAM 70-461
0
Comment
Question by:Mike Eghtebas
  • 4
  • 4
  • 2
10 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40623433
Query 2 is correct (for that requirement)

if you reference an outer joined table in the where clause you produce the effect of an inner join - OR - you have to provide for the ability to allow NULLs

So your Query1 is the same as an INNER JOIN (try it) example of "implicit inner join" here
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40623464
I had a typo in Query1, just to let you know:
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT JOIN Sales.Orders AS O
   ON C.custid = O.custid
WHERE o.orderdate >='20080101' AND o.orderdate <'20080301'

Also the book continues to say: This query returns 110 rows; here’s a portion of the output. but it returns 143 the one with 110 records is the one with WHERE clause.

This is copied right from the book:
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
LEFT OUTER JOIN Sales.Orders AS O
ON C.custid = O.custid
AND O.orderdate >= '20080201'
AND O.orderdate < '20080301';
This query returns 110 rows; here’s a portion of the output.
custid companyname orderid orderdate
------- --------------- -------- -----------------------
1 Customer NRZBB NULL NULL
2 Customer MLTDN NULL NULL
3 Customer KBUDE NULL NULL
4 Customer HFBZG 10864 2008-02-02 00:00:00.000
5 Customer HGVLZ 10866 2008-02-03 00:00:00.000
5 Customer HGVLZ 10875 2008-02-06 00:00:00.000
...
If you specify the date range

Open in new window


Query2 returns the following result which includes null dates. Is the acceptable at all?
1	Customer NRZBB	10835	2008-01-15 00:00:00.000
2	Customer MLTDN	NULL	NULL
3	Customer KBUDE	10856	2008-01-28 00:00:00.000
4	Customer HFBZG	10864	2008-02-02 00:00:00.000
5	Customer HGVLZ	10857	2008-01-28 00:00:00.000
5	Customer HGVLZ	10866	2008-02-03 00:00:00.000
5	Customer HGVLZ	10875	2008-02-06 00:00:00.000
5	Customer HGVLZ	10837	2008-01-16 00:00:00.000
6	Customer XHXJV	10853	2008-01-27 00:00:00.000
7	Customer QXVLA	10826	2008-01-12 00:00:00.000
8	Customer QUHWH	NULL	NULL
9	Customer RTXGC	10827	2008-01-12 00:00:00.000
9	Customer RTXGC	10876	2008-02-09 00:00:00.000
9	Customer RTXGC	10871	2008-02-05 00:00:00.000
10	Customer EEALV	NULL	NULL
11	Customer UBHAU	NULL	NULL
12	Customer PSNMQ	10881	2008-02-11 00:00:00.000
12	Customer PSNMQ	10819	2008-01-07 00:00:00.000
13	Customer VMLOG	NULL	NULL
14	Customer WNMAF	NULL	NULL
15	Customer JUWXK	NULL	NULL
16	Customer GYBBY	10848	2008-01-23 00:00:00.000
17	Customer FEVNN	10825	2008-01-09 00:00:00.000
18	Customer BSVAR	10890	2008-02-16 00:00:00.000
19	Customer RFNQC	NULL	NULL
20	Customer THHDP	10895	2008-02-18 00:00:00.000
20	Customer THHDP	10836	2008-01-16 00:00:00.000
20	Customer THHDP	10854	2008-01-27 00:00:00.000
21	Customer KIDPX	NULL	NULL
22	Customer DTDMN	NULL	NULL
23	Customer WVFAF	NULL	NULL
24	Customer CYZTN	10880	2008-02-10 00:00:00.000
24	Customer CYZTN	10824	2008-01-09 00:00:00.000
24	Customer CYZTN	10902	2008-02-23 00:00:00.000
25	Customer AZJED	10859	2008-01-29 00:00:00.000
26	Customer USDBG	10860	2008-01-29 00:00:00.000
27	Customer WMFEA	NULL	NULL
28	Customer XYUFB	NULL	NULL
29	Customer MDLWA	10887	2008-02-13 00:00:00.000
30	Customer KSLQF	10888	2008-02-16 00:00:00.000
30	Customer KSLQF	10911	2008-02-26 00:00:00.000
30	Customer KSLQF	10874	2008-02-06 00:00:00.000
30	Customer KSLQF	10872	2008-02-05 00:00:00.000
31	Customer YJCBX	NULL	NULL
32	Customer YSIQX	10816	2008-01-06 00:00:00.000
33	Customer FVXPQ	NULL	NULL
34	Customer IBVRG	10886	2008-02-13 00:00:00.000
34	Customer IBVRG	10903	2008-02-24 00:00:00.000
35	Customer UMTLM	10901	2008-02-23 00:00:00.000
35	Customer UMTLM	10863	2008-02-02 00:00:00.000
36	Customer LVJSO	NULL	NULL
37	Customer FRXZL	10912	2008-02-26 00:00:00.000
37	Customer FRXZL	10897	2008-02-19 00:00:00.000
38	Customer LJUCA	10829	2008-01-13 00:00:00.000
39	Customer GLLAG	10817	2008-01-06 00:00:00.000
39	Customer GLLAG	10849	2008-01-23 00:00:00.000
39	Customer GLLAG	10893	2008-02-18 00:00:00.000
40	Customer EFFTC	10858	2008-01-29 00:00:00.000
41	Customer XIIWM	10832	2008-01-14 00:00:00.000
42	Customer IAIJK	10810	2008-01-01 00:00:00.000
43	Customer UISOJ	NULL	NULL
44	Customer OXFRU	10862	2008-01-30 00:00:00.000
44	Customer OXFRU	10891	2008-02-17 00:00:00.000
45	Customer QXPPT	10884	2008-02-12 00:00:00.000
46	Customer XPNIK	10823	2008-01-09 00:00:00.000
46	Customer XPNIK	10899	2008-02-20 00:00:00.000
47	Customer PSQUZ	10811	2008-01-02 00:00:00.000
47	Customer PSQUZ	10838	2008-01-19 00:00:00.000
47	Customer PSQUZ	10840	2008-01-19 00:00:00.000
48	Customer DVFMB	10867	2008-02-03 00:00:00.000
48	Customer DVFMB	10883	2008-02-12 00:00:00.000
49	Customer CQRAA	10818	2008-01-07 00:00:00.000
50	Customer JYPSC	10892	2008-02-17 00:00:00.000
50	Customer JYPSC	10896	2008-02-19 00:00:00.000
51	Customer PVDZC	NULL	NULL
52	Customer PZNLA	NULL	NULL
53	Customer GCJSG	NULL	NULL
54	Customer TDKEG	10898	2008-02-20 00:00:00.000
55	Customer KZQZT	10808	2008-01-01 00:00:00.000
55	Customer KZQZT	10855	2008-01-27 00:00:00.000
56	Customer QNIVZ	10833	2008-01-15 00:00:00.000
57	Customer WVAXS	NULL	NULL
58	Customer AHXHT	NULL	NULL
59	Customer LOLJO	10844	2008-01-21 00:00:00.000
60	Customer QZURI	NULL	NULL
61	Customer WULWD	NULL	NULL
62	Customer WFIZJ	10868	2008-02-04 00:00:00.000
62	Customer WFIZJ	10913	2008-02-26 00:00:00.000
62	Customer WFIZJ	10914	2008-02-27 00:00:00.000
63	Customer IRRVL	10878	2008-02-10 00:00:00.000
63	Customer IRRVL	10865	2008-02-02 00:00:00.000
63	Customer IRRVL	10845	2008-01-21 00:00:00.000
64	Customer LWGMD	10828	2008-01-13 00:00:00.000
64	Customer LWGMD	10916	2008-02-27 00:00:00.000
65	Customer NYUHS	10889	2008-02-16 00:00:00.000
65	Customer NYUHS	10820	2008-01-07 00:00:00.000
65	Customer NYUHS	10852	2008-01-26 00:00:00.000
66	Customer LHANT	10812	2008-01-02 00:00:00.000
66	Customer LHANT	10908	2008-02-26 00:00:00.000
67	Customer QVEPD	10813	2008-01-05 00:00:00.000
67	Customer QVEPD	10851	2008-01-26 00:00:00.000
67	Customer QVEPD	10877	2008-02-09 00:00:00.000
68	Customer CCKOT	NULL	NULL
69	Customer SIUIH	NULL	NULL
70	Customer TMXGN	10831	2008-01-14 00:00:00.000
70	Customer TMXGN	10909	2008-02-26 00:00:00.000
71	Customer LCOUJ	10894	2008-02-18 00:00:00.000
71	Customer LCOUJ	10847	2008-01-22 00:00:00.000
71	Customer LCOUJ	10882	2008-02-11 00:00:00.000
71	Customer LCOUJ	10815	2008-01-05 00:00:00.000
72	Customer AHPOP	10869	2008-02-04 00:00:00.000
73	Customer JMIKW	NULL	NULL
74	Customer YSHXL	10907	2008-02-25 00:00:00.000
75	Customer XOJYP	10821	2008-01-08 00:00:00.000
76	Customer SFOGW	10846	2008-01-22 00:00:00.000
76	Customer SFOGW	10841	2008-01-20 00:00:00.000
76	Customer SFOGW	10885	2008-02-12 00:00:00.000
77	Customer LCYBZ	NULL	NULL
78	Customer NLTYP	NULL	NULL
79	Customer FAPSM	NULL	NULL
80	Customer VONTK	10842	2008-01-20 00:00:00.000
80	Customer VONTK	10915	2008-02-27 00:00:00.000
81	Customer YQQWW	10839	2008-01-19 00:00:00.000
81	Customer YQQWW	10830	2008-01-13 00:00:00.000
81	Customer YQQWW	10834	2008-01-15 00:00:00.000
82	Customer EYHKM	10822	2008-01-08 00:00:00.000
83	Customer ZRNDE	NULL	NULL
84	Customer NRCSK	10814	2008-01-05 00:00:00.000
84	Customer NRCSK	10843	2008-01-21 00:00:00.000
84	Customer NRCSK	10850	2008-01-23 00:00:00.000
85	Customer ENQZT	NULL	NULL
86	Customer SNXOJ	NULL	NULL
87	Customer ZHYOS	NULL	NULL
88	Customer SRQVM	10809	2008-01-01 00:00:00.000
88	Customer SRQVM	10905	2008-02-24 00:00:00.000
88	Customer SRQVM	10900	2008-02-20 00:00:00.000
89	Customer YBQTI	10904	2008-02-24 00:00:00.000
89	Customer YBQTI	10861	2008-01-30 00:00:00.000
90	Customer XBBVR	10879	2008-02-10 00:00:00.000
90	Customer XBBVR	10873	2008-02-06 00:00:00.000
90	Customer XBBVR	10910	2008-02-26 00:00:00.000
91	Customer CCFIZ	10906	2008-02-25 00:00:00.000
91	Customer CCFIZ	10870	2008-02-04 00:00:00.000

Open in new window

0
 
LVL 9

Assisted Solution

by:Ramanhp
Ramanhp earned 100 total points
ID: 40623507
Given Requirement :  Write a query that returns all customers, but match orders only if they were placed in February 2008.

Given Requirement Rephrased as below:
Show all order details of of all the Customers that have any order placed in Feb 2008.

So, this means, we are not bothered about those records where a customer may have placed any order except the month of Feb 2008.

This is therefore, as said by the previous member, is a case of clear inner join, that will chuck out the rest of the non-matching items which otherwise appears as null in left join.

Hope this info helps...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40623515
the requirement is ALL customers:
i.e. select COUNT(*) as [ThisIsTheNumberOfCustomers] from customers

so, any query that produces less than that many rows cannot meet the requirement

Query 1 (corrected)
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT JOIN Sales.Orders AS O
   ON C.custid = O.custid
WHERE o.orderdate >='20080101' AND o.orderdate <'20080301'

Open in new window


This contains a IMPLICIT INNER JOIN
because EVERY ROW MUST MEET THESE CRITERIA o.orderdate >='20080101' AND o.orderdate <'20080301'

NULL does NOT meet those criteria!

Query 1 can be modified to permit unmatched records e.g.
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT JOIN Sales.Orders AS O
   ON C.custid = O.custid
WHERE ( o.orderdate >='20080101' AND o.orderdate <'20080301' ) 
    OR O.custid IS NULL

Open in new window


I cannot vouch for the books' content. I suggest you build your own tests, it does not take many rows of data to do this.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40623519
I disagree with Ramanhp's analysis (sorry  Ramanhp)

the requirement is to list ALL customers

and, if they exist, match to orders they MIGHT HAVE placed during Feb. 2008 (so this needs an outer join)
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40623531
Basically, our date predicate could be True/False/Unknown for date part.

Show if o.orderdate >='20080101' AND o.orderdate <'20080301' is True
Don't Show if o.orderdate >='20080101' AND o.orderdate <'20080301' is False
Unknown: Show because?

All customers ought to be displayed unless the predicate is explicitly false? Which is not (it is unknown, it is null?)

Mike
0
 
LVL 9

Expert Comment

by:Ramanhp
ID: 40623545
If you disagree with my response,, then why you are pulling out the order details in your both queries.
If you rightly understand the question, then it says to get the list of Customers AND NOT THE DETAILS OF THEIR ORDERS, Further, it restricts to filter out only those ones that have made an order in Feb 2008.

What do you mean if they exists.... strange. You have your Customers database, and they exists there. And if they are the customers, then this means there is atleast one instance of order in your Orders DB.

Well, if you are sticking to the LEFT JOIN Logic. Then there is no need to write a complex query in this case.
Just have a simple Select Query on the Customers DB that will list out all the Customers details.

But if the concern is to get info some what like below...

Customer ID, Order in Feb2008
---------------------------------------------
Customer1, Yes
Customer2, No
Customer3, Yes.....

..and so on.. then there stands the INNER JOIN.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40623575
Thank you for all the comments.

FYI, I am trying to see why

I have to use:  ON C.custid = O.custid AND o.orderdate >='20080101' AND o.orderdate <'20080301'
not this: ON C.custid = O.custid WHERE o.orderdate >='20080101' AND o.orderdate <'20080301'

I am not trying to modify the choice of Left Join, I understand it maybe being discussed for purpose of discussion here.

The requirement: Write a query that returns all customers, but match orders only if they were placed in February 2008.

This to me if it means show the records is in Feb 2008, otherwise (if false or unknown) do not show. I will keep reading the comments over and over unless I could see why null values has to be a part of the output.

Mike
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points
ID: 40623608
Well, this is the problem with interpreting words as requirements.

I do not know "the book" so I can work only with what I have been given here. This is the "requirement" as I know it:
>>" Write a query that returns all customers, but match orders only if they were placed in February 2008. "

I interpret those words like this:

( return all customers ) & ( match orders ( if they were placed in February 2008 ) )

which is met  by:
FROM Customers
LEFT JOIN LEFT JOIN Sales.Orders AS O ON C.custid = O.custid
     AND o.orderdate >='20080101' AND o.orderdate <'20080301'

If you add words to that requirement such as "AND NOT THE DETAILS OF THEIR ORDERS" then the query would need alteration to suit. However the original question  - as I interpret the words - is satisfied by the left join containing conditions for the date range.

------
My only reference to inner join was to try and explain why placing the date range conditions into the where clause does not met the original requirement. When conditions such as those override an outer join this is can be called an "implicit inner join".  In the example of Query 1 and the requirement, that implicit inner join is wrong.

At no point above have I suggested you should use an inner join for the original requirement.
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40623672
Paul,

Thank you for the patience. Finally, I understood what is going on.
Write a query that returns all customers, ... <-- only this gives 832 records
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT JOIN Sales.Orders AS O
    ON C.custid = O.custid

Open in new window


...but match orders only if they were placed in February 2008. <--  gives 110 records, match dates but doesn't include customer who has not placed an order yet.
SELECT Count(*) FROM Sales.Orders AS O
WHERE o.orderdate >='20080101' AND o.orderdate <'20080301' 

Open in new window


Now, combining them we get 143 records using:
SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT JOIN Sales.Orders AS O
    ON C.custid = O.custid
AND o.orderdate >='20080101' AND o.orderdate <'20080301' 

Open in new window


Every word you type turn to gold, to me as valuable.

Thanks,

Mike
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now