This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC 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 Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "query help" Watch "query help" New topic
Author

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
Thankyou
 
Don't get me started about those stupid light bulbs.
 
subject: query help
 
Similar Threads
composite-id, one-to-one, formula, and an outer join
Linking two tables in JDBC
JPA composite key mapping
restrict queries in association while fetching data....
Hibernate: unexpected queries in parent/child relationship