find records with two entries in a table

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

find records with two entries in a table

Post by fgomez »

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!
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

Post by fgomez »

Actually it would be preferable if the query returned the IDs rather than the number of IDs...

No suggestions for this? I guess maybe I'll just try to have PHP do the legwork...
User avatar
veridicus
Forum Commoner
Posts: 86
Joined: Fri Feb 23, 2007 9:16 am

Post by veridicus »

Code: Select all

select id from table_name group by id having count(distinct activity) > 1
(Untested :) )
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

Post by fgomez »

Thanks! 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?
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

Post by fgomez »

OK, I feel like this is cheating, but I think this worked for me. I just joined the table on itself using a different alias for each instance of the table:

SELECT t1.id
FROM `table` t1, `table` t2
WHERE t1.id = t2.id
AND t1.id = 'A'
AND t2.id = 'B'

Am I supposed to be able to do that?!?!
User avatar
veridicus
Forum Commoner
Posts: 86
Joined: Fri Feb 23, 2007 9:16 am

Post by veridicus »

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
fgomez
Forum Commoner
Posts: 61
Joined: Mon Sep 26, 2005 11:23 pm
Location: Washington, DC

Post by fgomez »

Hm... that's a lot cleaner than my query. Pretty clever... and I think functionally equivalent... why didn't I think of that? :roll:
Post Reply