CODEWITHSUNDEEP
mysqlsql
Bazze
Bazze

Reputation: 775

Exclusive mysql select query, two tables

I have the following tables (they all got more columns but I'm just showing the ones of interest):

Product                         Order details                   Orders
----------------------------    ----------------------------    --------------
| id_product | id_supplier |    | id_order   |  id_product |    |  id_order  |
|     12     |     2       |    |     1      |     56      |    |     1      |
|     32     |     4       |    |     2      |     32      |    |     2      |
|     56     |     2       |    |     2      |     56      |    |     3      |
|     10     |     1       |    |     4      |     56      |    |     4      |
----------------------------    |     3      |     12      |    --------------
                                ----------------------------

What I want to do is select all orders which have products from ONLY one or more suppliers. So lets say I want all orders that only have products from the supplier with id 2 (id_supplier = 2) I should get the orders with id 1, 3 and 4.

If I want all orders that ONLY have products from the supplier with id 4 (id_supplier = 4) I should get an empty result.

If I want all orders that ONLY have products from the suppliers with id 2 AND 4 I should get the order with id 2.

I've read the following question: mySQL exclusive records but I can't get a grip of that query to work when I have two tables like I have. I just need another pair of eyes to help me out here! :)

Do you have any idea on how I'll do this?

EDIT: To clearify, I want to fetch all orders that ONLY contains products from one or more specified suppliers. Orders with products from other suppliers than is specified, should not be included.

Upvotes: 0

Views: 349

Answers (3)

DRapp
DRapp

Reputation: 48169

per the questions I've listed, I think THIS is what you want, and can be done with a LEFT join.

select 
      od.id_order,
      sum( if( p.id_supplier in ( 2, 4 ), 1, 0 )) as HasSupplierLookingFor,
      sum( if( p.id_supplier in ( 2, 4 ), 0, 1 )) as HasOtherSuppliers
   from
      order_Details od
         join product p
            on od.id_product = p.id_product
   group by
      od.id_order
   having 
          HasSupplierLookingFor > 0
      AND HasOtherSuppliers = 0

Sometimes, just answering a question that can be somewhat ambiguous as presented leads to misrepresented answers. This query will by a per order basis, join to the products to find the suppliers and group by the order id.

For each product ordered, the first SUM() asks if its one of the suppliers you ARE looking for, if so, sum a value of 1, otherwise 0... The next SUM() asks the same thing... but if it IS the supplier, use zero, thus all OTHER suppliers gets the 1.

So, now, the HAVING clause is looking for any order that at a minimum of 1 of your suppliers qualified AND it had no other suppliers represented.

So you could have an order with 30 items, and 20 from supplier 2, and 10 from supplier 4. The HasSupplierLookingFor would = 30, and HasOtherSuppliers = 0, the order would be included.

Another order could have 5 items. One from supplier 2, and 4 others from supplier 9. This would have HasSupplierLookingFor = 1, and HasOtherSuppliers = 4, thus exclude this as a qualified order.

Upvotes: 1

Manuel
Manuel

Reputation: 10303

SELECT o.id_order
FROM Orders o 
    INNER JOIN `Order details` od
        ON o.id_order = od.id_order
    INNER JOIN Product p
        ON p.id_product = od.id_product
WHERE p.id_supplier IN (2,4)

the (2,4) are the suppliers you want to fetch. you can also ask for only 1 by saying (2)

Upvotes: 0

Alex Siri
Alex Siri

Reputation: 2864

You should inner join all those tables, like this:

SELECT o.* from Orders o
   INNER JOIN Details d ON o.id_order = d.id_order
   INNER JOIN Products p ON d.id_product = p.id_product
   WHERE p.id_supplier = 4

That will give you the orders which include products from that supplier.

Upvotes: 0

Related Questions