How to get duplicate results in two or more joined tables?

I have two tables:

1. orders (id);
2. order_items(id, order_id, item_id, quantity)

Let's say I have already selected an order X with 3 different items (A - 1 unit, B - 2 units and C - 1 unit).

I need to get all the orders that have same items and exactly the same q开发者_如何学Gouantity of items (in this case: A - 1 unit, B - 2 units and C - 1 unit and no more or no less :)), excluding an order X.

i made a dump of test tables, so, I hope, for You would be easier to understand, what I'd like to get:) http://nopaste.info/44eb93ae3d.html

lets assume order X = 1, so desired output would be only the order 2, because order 3 has only one item and order 4 has same items as #1, but it has also one item extra, so it is not good :)

---

Count the same occurence of each orders in the order_items table, and then compare to the number of item of the searched order:

SELECT i2.order_id
FROM order_items i1
    RIGHT OUTER JOIN order_items i2
        ON i2.item_id = i1.item_id 
        AND i2.quantity = i1.quantity
        AND i1.order_id = 1
WHERE i2.order_id  != 1
GROUP BY i2.order_id
HAVING COUNT(i1.order_id) = COUNT(*)
   AND COUNT(i1.order_id) = (SELECT COUNT(*) FROM order_items WHERE order_id = 1)

Note that this query assume that (order_id, item_id) is a Key of the order_items table.

I have made a sample test here.

---

select *
from orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN (select *
      from orders o2
      JOIN order_items oi2 ON o2.id = oi2.order_id
      WHERE o2.id = @X) sub
ON oi.quantity = sub.quantity
AND oi.item_id = sub.item_id
WHERE o.id <> @X

EDIT:

select *
from orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN (select *
      from orders o2
      JOIN order_items oi2 ON o2.id = oi2.order_id
      WHERE o2.id = @X) sub
ON oi.quantity = sub.quantity
AND oi.item_id = sub.item_id
AND NOT EXISTS (select 1 
                from order_items 
                where order_id = o.id 
                and concat(item_id,quantity) not in (select concat(itemid,quantity)
                                                     from order_items
                                                     where order_id = o.id))
WHERE o.id <> @X