SELECTING ONE THING CONNECTED TO SEVERAL OTHERS IN DIFFERENT MYSQL TABLE

Chintoo Semi

Total Post:135

Points:947
Posted by  Chintoo Semi
MYSQL 
 1063  View(s)
Ratings:
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:

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!
  1. AVADHESH PATEL

    Post:604

    Points:4228
    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 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

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!