Home > DeveloperSection > Forums > Selecting one thing connected to several others in different MySQL table
Chintoo Semi

Total Post:135

Posted on    April-08-2013 2:10 AM


 1 Reply(s)
 1019  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 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
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 (
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!


Total Post:604

Posted on    April-08-2013 7:34 AM

Hi Chintoo!

try the help using joining as below

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

I hope it resolve your problem

Modified On Apr-08-2013 07:36:18 AM

Don't want to miss updates? Please click the below button!

Follow MindStick