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.