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

Answer 3

SELECT t4.id, COUNT(t4.id) AS frequency
FROM users t1
LEFT JOIN countryselection t2
ON t1.id = t2.userid
INNER JOIN countryselection t3 
  ON t2.userid != t3.userid AND t2.countryid = t3.countryid
INNER JOIN users t4 
  ON t3.userid = t4.id 
   AND ((t4.pref_m = 1 AND t1.gender = 'male' OR t4.pref_f = 1 AND t1.gender = 'female')
     AND (t1.pref_m = 1 AND t4.gender = 'male' OR t1.pref_f = 1 AND t4.gender = 'female'))
WHERE t1.id = ?
GROUP BY t4.id
ORDER BY frequency DESC

Similar to others here but using appropriate join types and join conditions instead of where conditions.

From MySQL docs:

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.

Answer 4

inferred DDL :

create table users (id int, gender text, pref_m bool, pref_f bool);
create table countryselection (id int, userid int, countryid int);

Here is CSV that you can .import (using sqlite3, after doing .separator ,) into the tables in the question:

users.csv:

1,male,1,0
2,male,1,1

countryselection.csv

1,1,123
2,1,111
3,1,100
4,1,110
5,2,123
6,2,111
7,2,202
8,2,210

peter's sql, edited to use field names from question:

SELECT        
    us2.id,
    COUNT(cs2.*) as countries_in_common
FROM
    countryselection cs1 
LEFT JOIN 
    countryselection cs2 ON
    (
        cs2.userid <> $userid AND 
        cs2.countryid = cs1.countryid 
    )
LEFT JOIN 
    users us1 ON 
    (
        us1.id = cs1.userid
    )
LEFT JOIN 
    users us2 ON
    (
        us2.id = cs2.userid
    )
WHERE
    cs1.userid = $userid AND 
    cs2.userid IS NOT NULL AND
    (
        (us1.pref_m = 1 AND us2.gender = 'male') 
        OR
        (us1.pref_f = 1 AND us2.gender = 'female') 
    ) AND
    (
        (us2.pref_m = 1 AND us1.gender = 'male')
         OR
        (us2.pref_f = 1 AND us1.gender = 'female') 
    )          
GROUP BY
    cs2.userid 
;

you can execute it like this:

sqlite3 myDBname < peters_sql.sql

setting $userid to 1, i get 2 as output.

Answer 5

I think this works

select me.id meid
     , them.id themid
     , me.gender mygender
     , them.gender themgender
     , me.pref_m mepref_m
     , me.pref_f mepref_f
     , them.pref_m thempref_m
     , them.pref_f thempref_f
     , csme.countryid

  from users me

 cross
  join users them

 inner
  join countryselection csme
    on me.id = csme.userid

 inner
  join countryselection csthem
    on them.id = csthem.userid

 where csme.countryid = csthem.countryid
   and ((me.gender = 'male' and them.pref_m) or (me.gender = 'female' and them.pref_f))
   and ((them.gender = 'male' and me.pref_m) or (them.gender = 'female' and me.pref_f))
   and me.id != them.id
   and me.id = 2

http://sqlfiddle.com/#!2/06351/25/0

I intentionally left out the group by so that the results are more easily verified.

Answer 6

UPDATE: (added the countries)

SELECT u1.id AS uid1
        , u2.id AS uid2
        , cs.countryid
FROM users u1
, users u2
JOIN countryselection cs ON cs.userid = u2.id
-- WHERE u1.id < u2.id -- tiebreaker
WHERE u1.id = 12345
AND EXISTS (
        SELECT * FROM countryselection cs1
        JOIN countryselection cs2 ON cs1.countryid = cs2.countryid
        WHERE cs1.userid = u1.id
        AND cs2.userid = u2.id
        )
AND ((u1.pref_m = True AND u2.gender = 'male')
        OR (u1.pref_f = True AND u2.gender = 'female') )
        -- the love must be mutual ...
AND ((u2.pref_m = True AND u1.gender = 'male')
        OR (u2.pref_f = True AND u1.gender = 'female') )
        ;