I think I'm just having a brain fart here, but if someone could help me out I'd appreciate. Maybe it's just too late in the day for thinking...
Say I have a table `actions` with a structure and data as such:
+----+--------+--------+ | id | userid | action | +----+--------+--------+ | 1 | a | foo | | 2 | a | bar | | 3 | a | baz | | 4 | b | foo | | 5 | c | foo | | 6 | c | bar | | 7 | d | foo | | 8 | d | bar | | 9 | d | baz | +----+--------+--------+
What I want to do is return all values of `userid` that have a 'foo' action but not a 'baz' action (those being b and c). I know I know the answer to this, but I'm drawing a blank at the moment.
Thanks in advance if someone could give me a hand.
Join the table to itself on userid, requiring action foo on the left and action baz on the right. Using a LEFT JOIN, the rows you want would be NULL on the right.
SELECT a1.userid FROM actions a1 LEFT JOIN actions a2 ON a1.userid = a2.userid AND a2.action='baz' WHERE a1.action = 'foo' AND a2.id IS NULL;
+--------+ | userid | +--------+ | b | | c | +--------+ 2 rows in set (0.01 sec)
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
