File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
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

Win a copy of Customer Requirements for Developers this week in the Jobs Discussion forum!
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][/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:
subject: query help
It's not a secret anymore!