CODEWITHSUNDEEP
mysqlsqljoin
Alex R
Alex R

Reputation: 93

How to exclude row in one-to-many relationship between tables (SQL query)

I have two tables, Products and ProductTags (I also have table Tags which is not part of the problem)

My query is

SELECT
product.id
FROM
Products JOIN ProductTags ON Products.id = ProductTags.product_id
WHERE ProductTags.tag_id = 10  and ProductTags.tag_id <> 20

Table ProductTags is one-to-many connection between product_id and tag_id, if it's called like that. Let's say that whole ProductTags table is:

product_id tag_id
777 10
777 20
888 10
888 30

what I get as an output from my query is 777 and 888. But I want to exclude products which have tag #20. In real query I also join with other tables (also I need to have access to other fields of Products table), so I CAN'T get proper result using only ProductTags table! I am aware that I could do it like

SELECT product_id ProductTags WHERE tag_id = 10

but this WON'T be proper solution!

Upvotes: 1

Views: 1106

Answers (2)

Akina
Akina

Reputation: 42739

SELECT product.id
FROM Products 
JOIN ProductTags ON Products.id = ProductTags.product_id
GROUP BY product.id
HAVING SUM(ProductTags.tag_id = 10) > 0 -- at least one
   AND SUM(ProductTags.tag_id = 20) = 0 -- none

This form allows any amount of simple or complex conditions.

For example:

-- strictly one row with tag_id = 30
   AND SUM(ProductTags.tag_id = 30) = 1
-- at least one tag_id 40 or 50
   AND SUM(ProductTags.tag_id IN (40, 50)) > 0
-- ... and so on

Upvotes: 1

sticky bit
sticky bit

Reputation: 37487

One way is to use aggregation.

SELECT product_id id
       FROM producttags
       WHERE tag_id IN (10,
                        20)
       GROUP BY product_id
       HAVING max(tag_id) = 10;

Another one uses NOT EXISTS and a correlated subquery.

SELECT pt1.product_id
       FROM producttag pt1
       WHERE pt1.tag_id = 10
             AND NOT EXISTS (SELECT *
                                    FROM producttag pt2
                                    WHERE pt2.product_id = pt1.product_id
                                          AND pt2.tag_id = 20);

Note that the join of product isn't needed to only get the product IDs, unless there's no proper foreign key constraint on producttags.product_id.

Upvotes: 2

Related Questions