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:
>

Help with a query

Hello all-

I am stuck with a query problem. I have two tables whose structure is described below.

If the attributename in @.fulltable matches the attributename in @.subtable then compare the correponding value columns and return the rows whose values match. If the attributename does not match, return the row anyway.

set nocount on

declare @.fulltable table
(itemid int,
attributename varchar(100),
value int)

declare @.subtable table
(attributename varchar(100),
value int)

insert into @.fulltable values (1, 'a', 100)
insert into @.fulltable values (1, 'b', 200)
insert into @.fulltable values (1, 'c', 300)
insert into @.fulltable values (2, 'a', 400)
insert into @.fulltable values (2, 'b', 500)

insert into @.subtable values ('a', 100)
insert into @.subtable values ('b', 500)

In the SQL above, my result should return

1 'a' 100 -- Because the value matches
1 'c' 300 -- Because the attributename is not present in @.subtable
2 'b' 500 -- Because the value matches

-- 1 'b' 200 and 2, 'a', 400 should be eliminated as the attributenames in @.fulltable match the attributenames in @.subtable, but the values dont.

I have tried something similar to:

SELECT ft.*
FROM @.fulltable ft
INNER JOIN @.subtable sub
ON ft.value = CASE WHEN ft.attributename = sub.attributename
THEN sub.value
ELSE ft.value
END

As you can see, it does not return the desired result. Please let me know if you need any further explanation of what I am trying to achieve. Any help is greatly appreciated.May be this can be the solution;

Try to write two different queries after this use them together ..

Query q1

SELECT fulltable.attributename, fulltable.value
FROM fulltable INNER JOIN
subtable ON fulltable.attributename = subtable.attributename AND fulltable.value = subtable.value

This will give
a 100
b 500

Query q2

SELECT fulltable.attributename, fulltable.value
FROM fulltable LEFT OUTER JOIN
subtable ON fulltable.attributename = subtable.attributename
WHERE (subtable.attributename IS NULL)

This will result
c 300

Query q3

SELECT * FROM q1 UNION SELECT * FROM q2

This will result
a 100
b 500
c 300

Hope this will solve your problem ..|||Sishe-

Your solution works great. I greatly appreciate your time. There is a little bit more to the problem. Let me explain.

ValueColumnName in the @.subtable gives the name of the column in the @.fulltable with which the value in the @.subtable should be compared. I have tried many different ways but still cannot get the solution to this problem. I greatly appreciate if you could help me with this query.

set nocount on

declare @.fulltable table
(itemid int,
attributename varchar(100),
valueInt int,
valueString varchar(256),
valueFloat float)

declare @.subtable table
(attributename varchar(100),
value varchar(256),
ValueColumnName varchar(100))

insert into @.fulltable values (1, 'a', NULL, 'String1', NULL)
insert into @.fulltable values (1, 'b', 200, NULL, NULL)
insert into @.fulltable values (1, 'c', NULL, NULL, 1.31)
insert into @.fulltable values (2, 'a', NULL, 'String2', NULL)
insert into @.fulltable values (2, 'b', 500, NULL, NULL)

insert into @.subtable values ('a', 'String1', 'ValueString')
insert into @.subtable values ('b', 500, 'ValueInt')

The ouput I am expecting is still similar as before.

1 'a' NULL 'String1' NULL -- Because the value matches
1 'c' NULL NULL 1.31 -- Because the attributename is not present in @.subtable
2 'b' 500 NULL NULL -- Because the value matches|||This should help:

select
f.*
from
@.fulltable f
left outer join
@.subtable s
on
s.value = case
when s.ValueColumnName = 'ValueInt' then cast(f.valueInt as varchar(100))
when s.ValueColumnName = 'ValueString' then f.valueString
when s.ValueColumnName = 'ValueFloat' then cast(f.valueFloat as varchar(100))
end
where
s.value is not null

union all

select
f.*
from
@.fulltable f
left outer join
@.subtable s
on
f.attributename = s.attributename
where
s.value is null

order by
f.itemid, f.attributename|||manowar did give the solution.. One thing I didn't see in first query is please use "UNION ALL" not "UNION" in first query. Because UNION works like DISTINCT keyword so it may cause wrong result sets.