Say I have an orders table consisting of id, customer_id, order_date and some other stuff. I also have an items table of things people can order consisting of item_id, item_name and price. And finally there is a table called c that connects order_id with item_id's.
What I would like to do is to get some data from the orders table on all orders containing certain items that I search for by name.
I could do something like this, but that is obviously a bad solution even with just two items, not to mention five or so. But I think it illustrates what I am looking for:
SELECT o.id, customer_id, order_date
FROM orders AS o, c AS c1, items AS i1, c AS c2, item AS i2
WHERE item_name = 'foo' AND c1.item_id = i1.id AND c1.order_id = o.id
AND item_name = 'bar' AND c2.item_id = i2.id AND c2.order_id = o.id
I have also seen a solution along the following lines, but it is somewhat slow too:
FROM orders as o
INNER JOIN c
ON o.id = c.order_id
WHERE o.id IN (
WHERE item_id IN (
FROM items as i
WHERE item_name IN (
GROUP BY o.id
HAVING COUNT(o.id) > 1
Could someone suggest a faster working solution? I assume this type of query is quite common, so I suppose there is at least one fast and clever solution. :)
Edit: This scenario is a somewhat simplified version of a real situation. One thing to take into consideration is that item_names aren't necessarily unique, which means that any solution that employs HAVING COUNT(...) > 1 will retrieve orders that contains 'foo' twice rather than at least one 'foo' and one 'bar'.
Thanks in Advance!