query help

[Dave Brown]

Hi All,

Puzzling over a query I have to write.
I have the following tables. Users, Orders, Products & order_products.

I need a resultset that contains ALL user information & also 1 additional column that indicates if the user has every placed an order of a product that has a productkey with a value < 1000.

I decided to start by first doing a query to return all orders ever made which are for products with the key < 1000 ..

select o.serialno,o.datestarted,o.userserial as datepurchased
from orders o
join order_products op on o.serialno = op.orderSerial
join products p on p.serialno = op.productserial
where p.productkey < 1000;

Which appears to work ok.. my problem now is doing a "select users.*," and creating the calculated column depending if the user serial appears in the above query.

Any help appreciated..

[stu derby]

A decent start.

I'll assume that your order has a column identify what user made the order, let's call that column USER_ID, since you didn't give us any table descriptions...

Something like:

select u.*, t.has_ordered_sub1000
from users u
outer join
(select distinct o.user_id, 'X' as has_ordered_sub1000
from orders o 
join order_products op on o.serialno = op.orderSerial
join products p on p.serialno = op.productserial
where p.productkey < 1000) t
on u.user_id = t.user_id;

[Dave Brown]

Thankyou