This week's book giveaway is in the Other Languages forum.
We're giving away four copies of Functional Reactive Programming and have Stephen Blackheath and Anthony Jones on-line!
See this thread for details.
Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

query help

 
Dave Brown
Ranch Hand
Posts: 301
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

 
Dave Brown
Ranch Hand
Posts: 301
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thankyou
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic