CODEWITHSUNDEEP
mysql
bruno
bruno

Reputation: 79

MySQL exclusive row select

I have a table called 'survey_product' with the following structure:

id int(11)
order_id int(11)
product_id int(11)
pq1 varchar(2)
pq2 varchar(2)
pq3 varchar(2)

This table stores products ordered through the system.

And here are some data records in that table:

(1, 2, 20, '1', '1', 'y')
(2, 2, 21, '1', 'y', 'y')
(3, 2, 22, '1', 'y', 'n')
(4, 2, 23, '1', 'y', 'y')
(5, 2, 24, '1', 'n', 'y')
(6, 3, 20, '1', 'n', 'y')
(7, 3, 24, '1', 'n', 'y')
(8, 3, 25, '1', 'n', 'y')
(9, 4, 20, '1', 'n', 'y')
(10, 4, 21, '1', 'n', 'y')
(11, 4, 23, '1', 'n', 'y')
(12, 4, 24, '1', 'n', 'y')

Above we have 3 orders (order_id 2, 3 and 4).

I need to get the order_id for all orders that had product_id = 21 BUT NOT product_id = 20 (the other product_id for in the order are irrelevant - I'm just interested in 21 and 20).

Then I need to know the orders that had BOTH product_id 20 and 21 (again, the other products in the order are irrelevant).

This is the basic query to pull all the orders for a specific product right now:

SELECT order_id FROM survey_product 
WHERE product_id = 20
AND (pq1 = '1' OR pq1 = '2' OR pq1 = '3')

Any ideas on how I can achieve that?

I have tried

SELECT order_id FROM survey_product 
WHERE product_id IN (20) AND product_id NOT IN (21)
AND (pq1 = '1' OR pq1 = '2' OR pq1 = '3')

But it is excluding all the other values as well.

Thanks a lot.

Upvotes: 3

Views: 766

Answers (2)

raina77ow
raina77ow

Reputation: 106453

It's usually beneficial to use JOINs instead of nested queries in such cases:

    SELECT sp.order_id
      FROM survey_product AS sp
 LEFT JOIN 
           (SELECT order_id FROM survey_product WHERE product_id = 20) AS sp_t
        ON sp.order_id = sp_t.order_id
     WHERE sp_t.order_id IS NULL
       AND sp.product_id = 21
       AND sp.pq1 IN ('1', '2', '3')

The query to get order_id having both 20 and 21 in product_id is similar: you just use INNER JOIN without any check for sp_t.order_id. Or just leave as it is, but replace IS NULL with IS NOT NULL if you're lazy - but that'll run slower, I suppose. )

Here's an SQL fiddle to play with.

Upvotes: 4

Nadir Sampaoli
Nadir Sampaoli

Reputation: 5555

I usually do this by using a nested query in WHERE condition:

SELECT order_id
FROM survey_product
WHERE product_id = 21
AND pq1 IN ('1', '2', '3')
AND order_id NOT IN (
    SELECT order_id 
    FROM survey_product
    WHERE product_id = 20)

Basically you find all order_ids from rows where product_id = 20 and then you exclude them from the main query, i.e. you select order_ids from rows where product_id = 21 but that are not in the order_ids resulting from the sub-query.

Upvotes: 3

Related Questions