Hi,
Is there any way to make the following LEFT OUTER JOIN statement work in a
similar fashion as the SUBQUERY statement that follows it? Customers 1 and
3
haven't ordered anything for the OrderDate '2006-01-01' and I want to exclud
e
them from the display in the LEFT OUTER JOIN query (like they are excluded i
n
the SUBQUERY statement).
select c.customerid
, c.customername
from customers c
left outer join orders o
on c.customerid = o.customerid
and o.orderdate = '2006-01-01'
customerid customername
-- ----
1 Brian
2 Andy
3 Steve
4 Delaney
select c.customerid
, c.customername
from customers c
where c.customerid not in
(select c.customerid from orders o
where c.customerid = o.customerid
and o.orderdate = '2006-01-01' )
customerid customername
-- ----
2 Andy
4 Delaney
Thanks,
IanHello, Ian
You can use this query:
select c.customerid
, c.customername
from customers c
left outer join orders o
on c.customerid = o.customerid
and o.orderdate = '2006-01-01'
WHERE o.customerid IS NULL
But I think it's better with a subquery (and it's more clear):
select c.customerid
, c.customername
from customers c
where c.customerid not in
(select o.customerid from orders o
where o.orderdate = '2006-01-01' )
Razvan|||Razvan
> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01' )
In this case we may want to add AND o.customerid IS NOT NULL
Just my 2 cents
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1139292357.248363.83490@.g14g2000cwa.googlegroups.com...
> Hello, Ian
> You can use this query:
> select c.customerid
> , c.customername
> from customers c
> left outer join orders o
> on c.customerid = o.customerid
> and o.orderdate = '2006-01-01'
> WHERE o.customerid IS NULL
> But I think it's better with a subquery (and it's more clear):
> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01' )
> Razvan
>|||Hi, Uri
You are right. Since the OP did show the DDL, I assumed that a
"customerid" column in an "orders" table would not allow NULL-s, but I
may be wrong... :) But in this case, I still think that he should fix
the DDL, not the query. Anyway, if we are not talking about orders and
customers, your comment raises a very good point.
Razvan|||Hi Razvan,
Thanks for the very quick answer. I should have tried that myself. I
actually simplified the query because what I really need to do is this: "Lis
t
all customers who have never ordered a particular product". Again, I can do
this via a Subquery with no problem but I'm told this can also be done via
LEFT OUTER JOINS and I can't get the following to work, so I must be missing
something.
SELECT c.CustomerID, p.productname
FROM Customers c
LEFT JOIN Orders O ON c.CustomerID = o.CustomerID
LEFT JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
LEFT JOIN Products p ON od.ProductNumber = p.ProductNumber
AND p.productname = 'Toshiba TV'
GROUP BY c.CustomerID,p.productname
ORDER BY c.CustomerID
The result set I get is this:
CustomerID productname
-- ----
1001 Toshiba TV
1001 NULL
1002 NULL
1003 Toshiba TV
1003 NULL
1004 Toshiba TV
1004 NULL
1005 NULL
1005 Toshiba TV
1006 NULL
1007 NULL
1007 Toshiba TV
1008 Toshiba TV
1008 NULL
1009 Toshiba TV
1009 NULL
1010 NULL
1010 Toshiba TV
What I want to be able to do is to eliminate CustomerIDs 1002 and 1006 from
the display because they have never ordered a "Toshiba TV". Adding a WHERE
clause of "WHERE c.CustomerID IS NULL" immediately before the GROUP BY claus
e
doesn't work (it results in 0 rows being returned).
Thanks,
Ian|||Hi guys,
Actually, adding an AND clause won't work here as it returns all four rows
that I mention in my original post. My original SUBQUERY without the AND
clause gives me what I want (i.e. just the two rows). Nulls are not allowed
for Customerid in the Order table.
> select c.customerid
> , c.customername
> from customers c
> where c.customerid not in
> (select o.customerid from orders o
> where o.orderdate = '2006-01-01'
and o.customerid IS NULL)
"Razvan Socol" wrote:
> Hi, Uri
> You are right. Since the OP did show the DDL, I assumed that a
> "customerid" column in an "orders" table would not allow NULL-s, but I
> may be wrong... :) But in this case, I still think that he should fix
> the DDL, not the query. Anyway, if we are not talking about orders and
> customers, your comment raises a very good point.
> Razvan
>|||Rats...I got it backwards. What I meant to say at the bottom of my post was
that I *only* want to report Customers 1002 and 1006 because they are the
only ones who have *not* ordered a Toshiba TV at some point in time.
"Ian Fisher" wrote:
> Hi Razvan,
> Thanks for the very quick answer. I should have tried that myself. I
> actually simplified the query because what I really need to do is this: "L
ist
> all customers who have never ordered a particular product". Again, I can
do
> this via a Subquery with no problem but I'm told this can also be done via
> LEFT OUTER JOINS and I can't get the following to work, so I must be missi
ng
> something.
> SELECT c.CustomerID, p.productname
> FROM Customers c
> LEFT JOIN Orders O ON c.CustomerID = o.CustomerID
> LEFT JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
> LEFT JOIN Products p ON od.ProductNumber = p.ProductNumber
> AND p.productname = 'Toshiba TV'
> GROUP BY c.CustomerID,p.productname
> ORDER BY c.CustomerID
> The result set I get is this:
> CustomerID productname
> -- ----
> 1001 Toshiba TV
> 1001 NULL
> 1002 NULL
> 1003 Toshiba TV
> 1003 NULL
> 1004 Toshiba TV
> 1004 NULL
> 1005 NULL
> 1005 Toshiba TV
> 1006 NULL
> 1007 NULL
> 1007 Toshiba TV
> 1008 Toshiba TV
> 1008 NULL
> 1009 Toshiba TV
> 1009 NULL
> 1010 NULL
> 1010 Toshiba TV
> What I want to be able to do is to eliminate CustomerIDs 1002 and 1006 fro
m
> the display because they have never ordered a "Toshiba TV". Adding a WHER
E
> clause of "WHERE c.CustomerID IS NULL" immediately before the GROUP BY cla
use
> doesn't work (it results in 0 rows being returned).
> Thanks,
> Ian|||Hello, Ian
Please post DDL (as CREATE TABLE statements) and sample data (as INSERT
INTO ... VALUES ...). See: http://www.aspfaq.com/etiquette.asp?id=5006
I think it's possible to do this with LEFT JOIN-s instead of a "NOT IN"
subquery, but it will require a derived table (a subquery in the FROM
clause) or a view.
Razvan|||Hi Razvan,
I figured it out with the help of a colleague....thanks a lot for your help
.
SELECT c.CustomerID, c.CustFirstName, c.CustLastName
FROM Customers c
LEFT JOIN
(SELECT c1.customerid as cid from customers c1
join orders o on o.CustomerID = c1.customerid
JOIN Order_Details od ON o.OrderNumber = od.OrderNumber
JOIN Products p ON od.ProductNumber = p.ProductNumber
WHERE p.productname = 'Viscount Microshell Helmet') c2
ON c2.cid = c.CustomerID
WHERE c2.cid IS NULL
Best Regards,
Ian|||> [...] what I really need to do is this: "List all customers
> who have never ordered a particular product".
> Again, I can do this via a Subquery with no problem
> but I'm told this can also be done via LEFT OUTER JOINS [...]
Since you did not provide DDL and sample data, I have used Northwind
for the following queries. After a little more thinking, I have found a
way to write this query using outer joins, even without using a derived
table.
So, instead of this simple and clear query:
SELECT CompanyName FROM Customers
WHERE CustomerID NOT IN (
SELECT CustomerID FROM Orders o
INNER JOIN [Order details] od ON o.OrderID=od.OrderID
INNER JOIN Products p ON od.ProductID=p.ProductID
WHERE p.ProductName='Chang'
)
we can write a query using LEFT JOIN-s, with a weird placement of the
ON clauses:
SELECT CompanyName FROM Customers c
LEFT JOIN Orders o
LEFT JOIN [Order details] od
LEFT JOIN Products p ON od.ProductID=p.ProductID
ON o.OrderID=od.OrderID
ON o.CustomerID=c.CustomerID
AND p.ProductName='Chang'
WHERE o.CustomerID IS NULL
or we can use a RIGHT JOIN:
SELECT CompanyName FROM Orders o
INNER JOIN [Order details] od ON o.OrderID=od.OrderID
INNER JOIN Products p ON od.ProductID=p.ProductID
RIGHT JOIN Customers c ON o.CustomerID=c.CustomerID
AND p.ProductName='Chang'
WHERE o.CustomerID IS NULL
However, if we examine the execution plans, we can see that the first
query (which is also easier to understand) has a lower cost than the
other two queries (which have the same execution plan).
For more informations, see:
http://groups.google.com/group/micr...br />
2579509b
http://groups.google.com/group/micr...br />
22a96b90
Razvan
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment