Counting the number of people who want to date each other

[worchyld]

I have a database and I have a table which contains
all the matches between males and females.

I require to count all the matches between males and females where
they BOTH say 'date'.

	Table setup;
	=============
	match_id	// primary key
	user_id_1	// points to a user
	user_id_2	// points to a user
	status    	// enum: 'date, no date'

For example, row 1 of my table has this info;

match_id = 33
user_id_1 = 1 // male user
user_id_2 = 2 // female user
status = 'date'

Row 2 has this info;

match_id = 33
user_id_1 = 2 // female user
user_id_2 = 1 // male user
status = 'date'

We can tell from above that both user_id_1 and user_id_2 want to date
each other.

How do I code this as a mysql command to count all those who match and want a date?

Many thanks for your help with this.

[swampBoogie]

select count(*) 
from matchTable t1 inner join matchTable t2
on t1.user_id_1 = t2.user_id_2
and t1.user_id_2 = t2.user_id_1
and t1.status = 'date'
and t2.status = 'date'

[worchyld]

Hey thanks alot swampBoogie, I'll try that!

Many thanks.