Skip to content
Advertisement

How to select data that doesn’t exist in other query

I’m trying to select from table data but i want the result not exist in this select.

(SELECT * FROM data WHERE category = 'A' ORDER BY RAND() LIMIT 4)
UNION ALL
(SELECT * FROM data WHERE category = 'B' ORDER BY RAND() LIMIT 4)` 

So there are no row that been selected double.
Fiddle with sample data.

Advertisement

Answer

With NOT EXISTS:

SELECT * FROM table1 t1
WHERE NOT EXISTS (
  SELECT 1 FROM (
    (SELECT * FROM table1 WHERE category = 'A' ORDER BY RAND() LIMIT 4)
    UNION ALL
    (SELECT * FROM table1 WHERE category = 'B' ORDER BY RAND() LIMIT 4)
  ) t 
  WHERE t.id = t1.id AND t.category = t1.category  
)

See the demo.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement