SQL statement I can't wrap my head around (brain too small)

Question

I'm writing sort of a travel-'dating' app.

• Users register themselves
• Users tell the app if they are male or female
• Users tell the app which countries they would like to visit
• Users tell the app if they want to travel with males (pref_m=1) or females (pref_f=1)

My tables

table 1: users

id (key) | gender | pref_m | pref_f
------------------------------------
 1          male     1        0
 2          male     1        1

table 2: countryselection

id (key) | userid | countryid
------------------------------------
 1          1        123
 2          1        111
 3          1        100
 4          1        110
 5          2        123
 6          2        111
 7          2        202
 8          2        210


So what the select statement has to do

Input: the userid of the current user
Output (in logic): SELECT the userids AND matching countries OF ALL people that want to travel to the same countries as I do, and want to travel with someone that has my gender
(join) Of that selection I obviously only need the people that are of the gender that I am looking for.
ORDERED by people that have the most matching countries with me DESC.

What I have so far (warning: not much)

$sql = "SELECT userid,count(*) AS matches from countryselection";
 $sql .= " WHERE countryid IN (SELECT countryid FROM countryselection WHERE userid = :userid) GROUP BY userid ORDER BY matches DESC;"; This gives me a list of all people that want to travel to the same countries as me (and how many countries we have in common)

final note

I'm obviously struggling with the gender-selection part.
Not sure if I have done the right thing to store the user selections in the way that I have.
I might need some guidance there too.

Obviously - thanks all.

Answer 1

SELECT        
    us2.id, -- etc. 
    COUNT(cs2.countryid) as countries_in_common               
FROM
    countryselection cs1 -- let's gather user countries he want to visit
LEFT JOIN -- now let's find other users!
    countryselection cs2 ON
    (
        cs2.userid <> :userid AND -- which are not him
        cs2.countryid = cs1.countryid -- and want to visit same countries
    )
INNER JOIN -- let's grab our user_data
    users us1 ON 
    (
        us1.id = cs1.userid
    )
INNER JOIN -- and let's grab other user data!
    users us2 ON
    (
        us2.id = cs2.userid
    )
WHERE
    cs1.userid = :userid AND -- finding our user countries he want to visit
    -- final checks
    (
        (us1.pref_m = 1 AND us2.gender = 'male') 
        -- he is looking for male and second user is male
        OR
        (us1.pref_f = 1 AND us2.gender = 'female') 
        -- he is looking for female and second user is female
    ) AND
    (
        (us2.pref_m = 1 AND us1.gender = 'male')
        OR
        (us2.pref_f = 1 AND us1.gender = 'female') 
    ) 
GROUP BY
    cs2.userid -- finally group by user_id

Best thing is there are no sub-queries, and you can easily use this query in many ways. (changing order, group by, and using aggregate functions)

Answer 2

It's pretty easy if you don't do the sorting by most countries in common (you could do it in code later if the result sets won't be too large):

SELECT
    o.id userid, u_cs.countryid
FROM users u
JOIN countryselection u_cs ON (u.id = u_cs.userid)
JOIN countryselection o_cs ON (u_cs.countryid = o_cs.countryid)
JOIN users o ON (o_cs.userid = o.id)
WHERE
    u.id = :userid AND   -- The user we want
    u.id <> o.id AND     -- Exclude ourselves
    (                    -- Check whether the other person is
                         -- compatible with us
        (u.pref_m = 1 AND o.gender = 'male') OR
        (u.pref_f = 1 AND o.gender = 'female')
    ) AND
    (                    -- Check whether we're compatible with the
                         -- other person
        (o.pref_m = 1 AND u.gender = 'male') OR
        (o.pref_f = 1 AND u.gender = 'female')
    )

SQL Fiddle

---

If you do want the sorting, I think the best option is to use GROUP_CONCAT (because MySQL sucks and doesn't support windowing/analytic functions).

SELECT
    o.id userid, GROUP_CONCAT(u_cs.countryid) countries
FROM users u
JOIN countryselection u_cs ON (u.id = u_cs.userid)
JOIN countryselection o_cs ON (u_cs.countryid = o_cs.countryid)
JOIN users o ON (o_cs.userid = o.id)
WHERE
    u.id = :userid AND   -- The user we want
    u.id <> o.id AND     -- Exclude ourselves
    (                    -- Check whether the other person is
                         -- compatible with us
        (u.pref_m = 1 AND o.gender = 'male') OR
        (u.pref_f = 1 AND o.gender = 'female')
    ) AND
    (                    -- Check whether we're compatible with the
                         -- other person
        (o.pref_m = 1 AND u.gender = 'male') OR
        (o.pref_f = 1 AND u.gender = 'female')
    )
GROUP BY o.id
ORDER BY COUNT(u_cs.countryid) DESC

You could probably pull this off with some nasty subqueries too, but I get the feeling it'll kill performance.

SQL Fiddle