Hi Everyone!

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:

SELECT o.id

FROM orders as o

INNER JOIN c

ON o.id = c.order_id

WHERE o.id IN (

SELECT order_id 

    FROM c 

    WHERE item_id IN (

        SELECT i.id

            FROM items as i

            WHERE item_name IN (

                'foo','bar'

            )

    )

)

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!