Need Some Help with a join Query

[Gregg Bolinger]

I have the following:

transaction_header
-----------------------
id
transaction_detail
-----------------------
id
header_id
product_id

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:

select header.id
from transaction_header header
join transaction_detail detail on detail.header_id = header.id
join product p on p.id = detail.product_id
where p.id = ?
and p.id = ?

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]

Does the answer have to involve joins? Because I would do something like this:
select header.id from transaction_header header where header.id in (select detail.header_id from transaction_detail detail where detail.product_id = ?) and header.id in (select detail.header_id from transaction_detail detail where detail.product_id = ?)

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]

 

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 Vashko]

My take on it with joins (the table needs to be joined twice):
select header.id from transaction_header header inner join transaction_detail detail1 on detail1.header_id = header.id inner join transaction_detail detail2 on detail2.header_id = header.id where detail1.product_id=? and detail2.product_id=?
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...)