jQuery in Action, 3rd edition
The moose likes JDBC and Relational Databases and the fly likes query help Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "query help" Watch "query help" New topic

query help

Dave Brown
Ranch Hand

Joined: Mar 08, 2005
Posts: 301
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..

Regards, Dave Brown
SCJP 6 - [url]http://www.dbws.net/[/url] - Check out Grails Forum
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
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

Joined: Mar 08, 2005
Posts: 301
I agree. Here's the link: http://aspose.com/file-tools
subject: query help
jQuery in Action, 3rd edition