Help with a SQL statement

My problem can best be described using the Northwind db as an example.
I would like to select all Customers that have never ordered a
particular Product. Thank you.There are several way to do this
(Don′t have the Northwind here but is think I get the column names right :-
) )
Select * from Customers C
Where not Exists
(
Select * from orders O where O.CustomerId = C.CustomerId
)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"rstewart27104@.gmail.com" wrote:

> My problem can best be described using the Northwind db as an example.
> I would like to select all Customers that have never ordered a
> particular Product. Thank you.
>|||Well, wouldn't you need to tie in orderdetails and productid, since the OP
wants customers that haven't ordered a specific product, not customers who
haven't ordered at all?
(BTW, how many customers that have never ordered, would you expect to find
in the database? :-) )
A
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:944AFED5-16A1-49C3-AA3E-190857243EEB@.microsoft.com...
> There are several way to do this
> (Don′t have the Northwind here but is think I get the column names right
> :-) )
> Select * from Customers C
> Where not Exists
> (
> Select * from orders O where O.CustomerId = C.CustomerId
> )
> --
> HTH, Jens Suessmeyer.|||Well If you want to make them an offer you sure have the customer in the
database with a customerid...
Didn′t saw the word "particular", then you should go by this:
Select * from Customers C
Where not Exists
(
Select * from orders O
inner join [Order Details] od on od.orderid = o.orderid
inner join [Products] P on o.productid = od.productid
where p.ProductName = 'SomeProduct'
and O.CustomerId = C.CustomerId
)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, wouldn't you need to tie in orderdetails and productid, since the OP
> wants customers that haven't ordered a specific product, not customers who
> haven't ordered at all?
> (BTW, how many customers that have never ordered, would you expect to find
> in the database? :-) )
> A
>
>
> "Jens Sü?meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
> wrote in message news:944AFED5-16A1-49C3-AA3E-190857243EEB@.microsoft.com..
.
>
>|||> Well If you want to make them an offer you sure have the customer in the
> database with a customerid...
Right, but my point was, most online stores won't have data about a customer
unless they've purchased from them once. :-)|||OK, but what would you expect from a query displaying all customers which
didn′t by anything if you refer to the online store example. If you have on
e
customer that′ll be
mankind - 1 = SelectList
;-D
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Aaron Bertrand [SQL Server MVP]" wrote:

> Right, but my point was, most online stores won't have data about a custom
er
> unless they've purchased from them once. :-)
>
>|||> OK, but what would you expect from a query displaying all customers which
> didn′t by anything if you refer to the online store example.
Typically, I wouldn't expect such a query to have any practical meaning. I
suppose there is probably a sliding scale, the less credible the company,
the lower their percentage of actual customers in their "customer" list.
:-)|||or
SELECT c.[CustomerID]
FROM [Customers] c
LEFT OUTER JOIN [Orders] o
ON c.[CustomerID]=o.[CustomerID]
WHERE o.[CustomerID] is null
"Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:944AFED5-16A1-49C3-AA3E-190857243EEB@.microsoft.com...
> There are several way to do this
> (Dont have the Northwind here but is think I get the column names right
:-) )
> Select * from Customers C
> Where not Exists
> (
> Select * from orders O where O.CustomerId = C.CustomerId
> )
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "rstewart27104@.gmail.com" wrote:
>