Databases
StackOverflow2013
Postsdbo
POSQL statement I can't wrap my head around (brain too small)
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL statement I can't wrap my head around (brain too small)
    text
    copied!<p>I'm writing sort of a travel-'dating' app.</p> <ul> <li> Users register themselves <li> Users tell the app if they are male or female <li> Users tell the app which countries they would like to visit <li> Users tell the app if they want to travel with males (pref_m=1) or females (pref_f=1) </ul> <h2>My tables</h2> <p><b>table 1: users</b></p> <p><code>id (key) | gender | pref_m | pref_f<br> ------------------------------------<br> &nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;male&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0<br> &nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;male&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1</code><br><br></p> <p><b>table 2: countryselection</b><br></p> <p><code>id (key) | userid | countryid <br> ------------------------------------<br> &nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;123<br> &nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;111<br> &nbsp;3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;100<br> &nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;110<br> &nbsp;5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;123<br> &nbsp;6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;111<br> &nbsp;7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;202<br> &nbsp;8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;210<br></code><br></p> <h2>So what the select statement has to do</h2> <p><b>Input:</b> the userid of the current user<br> <b>Output (in logic):</b> 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<br> <i>(join)</i> Of that selection I obviously only need the people that are of the gender that I am looking for.<br> ORDERED by people that have the most matching countries with me DESC. <br></p> <h2>What I have so far (warning: not much)</h2> <p><br><code> $sql = "SELECT userid,count(*) AS matches from countryselection";<br> &nbsp;$sql .= " WHERE countryid IN (SELECT countryid FROM countryselection WHERE userid = :userid) GROUP BY userid ORDER BY matches DESC;"; </code> <i>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)</i> </p> <h2>final note</h2> <p>I'm obviously struggling with the gender-selection part.<br> Not sure if I have done the right thing to store the user selections in the way that I have.<br> I might need some guidance there too.<br></p> <p><br> <b>Obviously - thanks all.</b></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload