Win a copy of Think Java: How to Think Like a Computer Scientist this week in the Java in General forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need Some Help with a join Query

 
Gregg Bolinger
GenRocket Founder
Ranch Hand
Posts: 15302
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Paul Clapham
Sheriff
Posts: 21107
32
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 187
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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...)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic