SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Counting the number of people who want to date each other

    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'.

    Code:
    	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.

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    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'

  3. #3
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey thanks alot swampBoogie, I'll try that!

    Many thanks.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •