This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Need Some Help with a join Query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Need Some Help with a join Query" Watch "Need Some Help with a join Query" New topic
Author

Need Some Help with a join Query

Gregg Bolinger
GenRocket Founder
Ranch Hand

Joined: Jul 11, 2001
Posts: 15286
    
    6

I have the following:



I need to write a query such that I find all the header rows that contain details with 2 specific products. I have the following so far:



This isn't getting what I expect. I want header rows with at least 2 detail rows. Of those 2, each one should point to the product IDs I am specifying.

Thanks.


GenRocket - A Test Data Generation Platform
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18121
    
    8

Does the answer have to involve joins? Because I would do something like this:


I'm sure that code could be recast in terms of joins, but I wouldn't because I find it more readable the way I wrote it.
Gaurangkumar Khalasi
Ranch Hand

Joined: Jun 02, 2012
Posts: 186
Gregg Bolinger wrote:I want header rows with at least 2 detail rows. Of those 2, each one should point to the product IDs I am specifying.

You can add "group by header.id having count(detail.header_id)>=2" at the end of your query...May you get your answer???...
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3434
    
  47

My take on it with joins (the table needs to be joined twice):

For simplicity I've omitted the join to the products table. Assuming the transaction_detail does not contain product_ids which would not be present in the products table, that join is not necessary (won't eliminate any rows).

(Hope I didn't get it wrong...)
 
Consider Paul's rocket mass heater.
 
subject: Need Some Help with a join Query
 
Similar Threads
HQL Query
Adding an element to a Treeset( was Plz clear My doubt ......)
JPA question
Querying tables with a one-to-many foreign key relationship
JOIN syntax for HQL . Need help!