I’m trying to select from table data but i want the result not exist in this select.
x
(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.