CODEWITHSUNDEEP
mysqlsql
Nicko
Nicko

Reputation: 11

SQL - How can I exclude results without subselect?

I stuck with sql query to list customer who bought product a but did not buy product b. This is my table

Table customer

id_customer   customer_name
1             name1
2             name2
3             name3

Table order

id_order   id_customer  product
1          1            a
2          1            b
3          2            b
4          3            a

I try:

SELECT * FROM customer, order WHERE customer.id_customer = order.id_customer
AND (order.product='a' AND order.product<>'b')

SELECT * FROM customer, order WHERE customer.id_customer = order.id_customer
AND (order.product IN ('a') AND order.product NOT IN ('b'))
[AND (order.product = 'a' AND order.product <> 'b')]

SELECT table1.id_customer, table1.customer_name FROM customer INNER JOIN order ON customer.id_customer = order.id_customer
WHERE order.product IN ('a') AND order.product NOT IN ('b')
[WHERE order.product = 'a' AND order.product <> 'b']

but it did not the right answer because it return:

1          1            a
4          3            a

The answer should be:

4          3            a

Anyone help me please. Thank you so much

Upvotes: 1

Views: 809

Answers (2)

onedaywhen
onedaywhen

Reputation: 57023

I don;t think we have all the details so here is a query that uses subqueries (ignoring question title) and returns customers rather than orders (ignoring expected resultset):

SELECT * 
  FROM customer AS c
 WHERE EXISTS (
               SELECT * 
                 FROM order AS o
                WHERE o.id_customer = c.id_customer  
                      AND product = 'a'
              )
       AND NOT EXISTS (
                       SELECT * 
                         FROM order AS o
                        WHERE o.id_customer = c.id_customer  
                              AND product = 'b'
                      );

Upvotes: 0

mellamokb
mellamokb

Reputation: 56769

You can use joins to filter out whether customer has each of product a, b, and then query the join to implement your particular logic. It would look something like this:

select distinct   -- pull only unique customer information
    C.*
from
    customer C
left join   -- orders of product a, which should exist
    order OA on OA.id_customer = C.id_customer and OA.product = 'a'
left join   -- orders of product b, which should not exist
    order OB on OB.id_customer = C.id_customer and OB.product = 'b'
where       -- orders of product a should exist
    OA.id_order is not null
and         -- orders of product b should not exist
    OB.id_order is null

Upvotes: 3

Related Questions