Hello,
I have a table that has two columns: 'id', and 'activity.' The 'id' references an auto-increment field in another table. The 'activity' is a code for an activity the member participated in.
The table might look like this:
id | activity
0034 | A
0048 | B
0034 | B
0091 | A
I want to write a query that returns the number of members who have done both activity 'A' and 'B.' In this case, it would return 1, because only id 0034 has participated in both activities.
Can anyone help me out?
Thanks!
find records with two entries in a table
Moderator: General Moderators
Code: Select all
select id from table_name group by id having count(distinct activity) > 1Thanks! This got me pretty close, but I guess my table example was incomplete. 'A' and 'B' are not the only two activity types possible. They may have participated in 'C' or 'D' as well.
All I'm interested in is members who have participated in 'A' and 'B'. What else (if anything) they've done is irrelevant to my purpose.
Any other ideas?
All I'm interested in is members who have participated in 'A' and 'B'. What else (if anything) they've done is irrelevant to my purpose.
Any other ideas?
Sure, you could join a table to itself, but in this case wouldn't a WHERE clause work?
Code: Select all
select id from table_name where activity in ('A', 'B') group by id having count(distinct activity) > 1