How to get orders with a specific item via SQL?

Question

MySQL

There is a table with orders (orders) and a table with order goods (order_positions).
How to get a list of orders where there is product 2 and no other products?
How to get a list of orders where there is product 2 and another product 3?
How to get a list of orders where there is product 2 and another product 3 or 4?
How to approach the task correctly? Thank you!

Answer 1

You would at least write the structure of the tables. In telepath mode, I can assume that
order_positions has the order_id, good_id fields and I want to believe that this is a unique key.
Then like this:

select order_id from order_positions
group by order_id
having count(good_id) = 1 and good_id = 2

* this is a shit code query with implicit behavior, but in mysql it will usually work.
see https://dev.mysql.com/doc/refman/5.0/en/sql-mode.h...

select order_id from order_positions where 
good_id IN (2,3)
group by order_id
having count(good_id) = 2

select order_id from order_positions where 
good_id IN (2,3)
group by order_id
having count(good_id) = 2
union
select order_id from order_positions where 
good_id IN (2,4)
group by order_id
having count(good_id) = 2

In the most general form, one can write

select * from order 
where 
order_id IN (select order_id from order_position WHERE good_id = 2)
AND
order_id NOT IN (select order_id from order_position WHERE good_id in (3,4))

This query will not use indexes, but it is the most readable)