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 (…

Accepted Answer

```
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)

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

Solution

Problem

Related problems