Selecting one thing connected to several others in different MySQL table

Posted by  Chintoo Semi
 1791  View(s)
Rate this:
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, 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 = AND c1.order_id =
AND item_name = 'bar' AND c2.item_id = AND c2.order_id =
I have also seen a solution along the following lines, but it is somewhat slow too:

FROM orders as o
ON = c.order_id
SELECT order_id 
    FROM c 
    WHERE item_id IN (
            FROM items as i
            WHERE item_name IN (
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!
  1. Re: Selecting one thing connected to several others in different MySQL table

    Hi Chintoo!

    try the help using joining as below

    SELECT orders.*
      FROM orders
        JOIN c
          ON = c.order_id
        JOIN item
          ON c.item_id =
      WHERE item_name IN ('foo', 'bar')
      GROUP BY;

    I hope it resolve your problem

      Modified On Mar-29-2018 11:54:11 PM