CODEWITHSUNDEEP
mysqlselect
Anand Shah
Anand Shah

Reputation: 14913

Inverting result of select query

I have a table called "products", with the following data.

--------------------
| id | tag_name    |
--------------------
| 1  | electronics |
| 1  | tv          | 
| 1  | lcd         |
| 2  | tv          | 
| 2  | lcd         |
| 3  | tv          |
| 3  | lcd         |
--------------------

It is mandatory that all the products have a "electronics" tag, but few of the products are missing that tag. Based on the data above my question, what query do I run so that it will return ids 2 & 3 as they don't have an 'electronics' tag.

2 solutions that come to mind are

  1. Using a NOT IN - select distinct id from products where id not in (select id from products where tag_name='electronics') - with a good few million rows this is quite inefficient

  2. Second solution is to cobble up a script which selects all distinct ids and then checks if "electronics" tag exists for that id, inserting the row if not.

Is there another more efficient way of handling this? (may be using a JOIN on the same table)

Upvotes: 0

Views: 2478

Answers (4)

Zane Bien
Zane Bien

Reputation: 23125

INSERT INTO products 
SELECT a.id, 'electronics'
FROM products a
LEFT JOIN products b ON a.id = b.id AND b.tag_name = 'electronics'
WHERE b.id IS NULL
GROUP BY a.id

This will actually update the products table to give the 'electronics' tag to all products which don't have it.

http://sqlfiddle.com/#!3/f312a/1/0

However, if you only wish to select the ids which don't have the particular tag, then use zerkms's solution.

Upvotes: 2

John Woo
John Woo

Reputation: 263713

JOIN will not work on your case since not all ids have electronics tag_name on it.

Just give this a try:

  SELECT DISTINCT id
    FROM Products
GROUP BY id
  HAVING not GROUP_CONCAT(tag_name) like CONCAT('%','electronics','%')

view here from demo: SQLFiddle.com

Upvotes: 2

Bryan
Bryan

Reputation: 6752

How about something like this, I tried it locally and it appeared to work

SELECT DISTINCT p1.id 
    FROM products p1 
    LEFT JOIN products p2 ON p1.id = p2.id AND p1.tag_name != p2.tag_name 
WHERE p1.tag_name != 'electronics' and p2.id IS NULL

Upvotes: 0

zerkms
zerkms

Reputation: 254916

SELECT DISTINCT t1.id
FROM products t1
LEFT JOIN products t2 ON t2.tag_name = 'electronics' AND t1.id = t2.id
WHERE t2.id IS NULL

http://sqlfiddle.com/#!2/8d805/2

Upvotes: 1

Related Questions