Inner Join experts out there??

[news-east.earthlink.net]

The scenario:

two tables

CustomerTable
---------------
CustomerID
OrderID
CustomerName
CustomerEmail
CustomerPhone

OrderTable
---------------
OrderID
ProductID
ProductName
ProductCost

This database was handed to me and I was asked to solve a problem - it looks
like an inner join solution would apply, but I'm not 100% sure.

There are 14 products total (numbers 1 through 14).
I'm looking to get a list of all the customers who have ordered product #1,
UNLESS they've ordered product #14 in which case I don't want to know about
that customer at all.

Any help would be greatly appreciated! I'll watch the newsgroup for the
answer - hopefully your response can help someone else too. However, if you
prefer to email me directly, you can send it to me at bunchah at yahoo dot
com.

Thanks in advance!

(if it'll help, I'll buy the person offering the correct solution a beer -
pending age verification of course) ;)

-Al

[news-east.earthlink.net]

select DISTINCT ct.cart_id
from OrderTable ct
inner join CartTable ot
on (ct.cart_id = ot.cart_id)
group by ct.cart_id
having count(case ot.product_inde x when 1 then 1 else null end) > 0
and count(case ot.product_inde x when 14 then 1 else null end) = 0
order by ct.cart_id;

Thanks Andy, that worked PERFECTLY!
Ok, now what kind of beer do you drink? (or wine?)

Dave - thanks to you too - it's nice to know I'm not the only one working on
a Friday night. :)

-Al

"Andy Hassall" <an**@andyh.co. uk> wrote in message
news:du******** *************** *********@4ax.c om...

On Sat, 22 Nov 2003 02:06:15 +0000, Andy Hassall <an**@andyh.co. uk> wrote:

A variation on Dave's query to account for MySQL's limitations and quirks[...] cannot reference aggregates in HAVING clauses only
their aliases, [...]
OK, that's wrong, looks like I was thinking of something else. So going

closer back to Dave's query:

select DISTINCT ct.CustomerID
from CustomerTable ct
inner join OrderTable ot
on (ct.OrderID = ot.OrderID)
group by ct.CustomerID
having count(case ot.ProductID when 1 then 1 else null end) > 0
and count(case ot.ProductID when 14 then 1 else null end) = 0
order by ct.CustomerID;

--
Andy Hassall (an**@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Raghuraman]

"news-east.earthlink. net" <ab************ ******@yahoo.no spam.com> wrote in message news:<nr******* ***********@new sread2.news.atl .earthlink.net> ...

The scenario:

two tables

CustomerTable
---------------
CustomerID
OrderID
CustomerName
CustomerEmail
CustomerPhone

OrderTable
---------------
OrderID
ProductID
ProductName
ProductCost

This database was handed to me and I was asked to solve a problem - it looks
like an inner join solution would apply, but I'm not 100% sure.

There are 14 products total (numbers 1 through 14).
I'm looking to get a list of all the customers who have ordered product #1,
UNLESS they've ordered product #14 in which case I don't want to know about
that customer at all.

Any help would be greatly appreciated! I'll watch the newsgroup for the
answer - hopefully your response can help someone else too. However, if you
prefer to email me directly, you can send it to me at bunchah at yahoo dot
com.

Thanks in advance!

(if it'll help, I'll buy the person offering the correct solution a beer -
pending age verification of course) ;)

-Al

Hi ,
Here is my reply

select customertable.* c1 from customertable inner join ordertable o1
on c1.orderid = o1.orderid where c1.orderid = 1 and o1.orderid <> 14

With Thanks
Raghuraman