I have this table, and I want to select all distinct user_id values where there is both an event='AAAAAAAAAA' and event ='XXXXXXXXXX' (i.e. only result should be user_id=123456789)
unique_row_id user_id event event_timestamp
----------------------------------------------------------------
0 123456789 AAAAAAAAAA 2010-01-20 15:00:00
1 123456789 abcdefghij 2010-01-20 15:00:05
2 123456789 XXXXXXXXXX 2010-01-20 15:00:15
3 987654321 AAAAAAAAAA 2010-01-20 16:00:00
4 987654321 abcdefghij 2010-01-20 16:00:05
5 987654321 abcdefghij 2010-01-20 16:00:15
6 111111111 XXXXXXXXXX 2010-01-20 16:01:00
7 111111111 XXXXXXXXXX 2010-01-20 16:01:05
8 111111111 XXXXXXXXXX 2010-01-20 16:01:15
I've tried both:
SELECT distinct user_id from mydata where event='AAAAAAAAAA' and event='XXXXXXXXXX'
which gives me an empty result set, and
SELECT distinct user_id from mydata where event='AAAAAAAAAA' or event='XXXXXXXXXX'
which gives me everything. Any suggestions - I'm guessing I need to go down the 'join' road here do I?
解决方案
Try this:
select user_id from mydata where event='AAAAAAAAAAA'
union
select user_id from mydata where event='XXXXXXXXXXX';
This will select both lists (users w/ AAA, users w/ XXX), and then merge them together, removing duplicate rows.
Hope that helps!
Thanks,
Joe