StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><a href="http://sqlfiddle.com/#!2/2e112/1/0" rel="nofollow">http://sqlfiddle.com/#!2/2e112/1/0</a></p> <pre><code>select * from stuff where ID in ( select ID from stuff where (question='gender' and answer in ('male','female')) or (question='eyecolor' and answer='grey') group by ID having count(ID)=2 ) </code></pre> <p>where <code>2</code> is the number of conditions in the nested <code>where</code> statement. If you run that nested select on its own, it will give you just a distinct list of ID's that fit the conditions. The outer statement allows the query to return all records for the ID's that fit those conditions. </p> <p><em>i edited this because.... i was wrong before</em></p> <hr> <p>k... <a href="http://sqlfiddle.com/#!2/2f526/1/0" rel="nofollow">http://sqlfiddle.com/#!2/2f526/1/0</a></p> <pre><code>select * from stuff where (question='gender' and answer in ('male','female')) or (question='eyecolor' and answer='grey') or (question='city' and answer in ('madrid','amsterdam')) </code></pre> <p>for this query, we return one row that matches any of those conditions for any ID. only ID's that satisfy at least one of those conditions will appear in the results. </p> <pre><code>select ID, count(*) as matches from stuff where (question='gender' and answer in ('male','female')) or (question='eyecolor' and answer='grey') or (question='city' and answer in ('madrid','amsterdam')) group by ID; </code></pre> <p>then we add the group by, so we can see how many rows are returned for each user and how many conditions they met (<code>count(*)</code>). </p> <pre><code>select ID from stuff where (question='gender' and answer in ('male','female')) or (question='eyecolor' and answer='grey') or (question='city' and answer in ('madrid','amsterdam')) group by ID having count(ID)=3; </code></pre> <p>the <code>having count(ID)=3;</code> is what makes this query work. we only want ID's that had 3 rows returned because we have 3 conditions. </p> <p><strong>and</strong>.... we can't use <code>and</code> because no row in that table will ever meet more than one of those conditions at a single time. <code>question</code> cannot be gender, eyecolor and city all at the same time. it has to do with your table layout. city will never be both madrid <em>and</em> amsterdam at the same time.... <code>and</code> will give us nothing. so... by using the <code>having</code> and an <code>or</code>... we can do stuff that's happy...?</p> <hr> <p>and to go on a tangent.... if your table looked like this: </p> <pre><code>ID gender eyecolor city --------------------------------------------- 100 male blue madrid 200 female grey amsterdam 300 male brown somewhere </code></pre> <p>you would use <code>and</code> because.... </p> <pre><code>select * from table where gender in ('male','female') and city in ('madrid','amsterdam') and eyecolor = 'grey' </code></pre> <p>but your table is a special one and didn't want to go that way because you really shouldn't have a column for every question... what if they change or what if you add 20? that'd be hard to maintain. </p> <hr> <p>and....</p> <pre><code>select ID from stuff where question in ('gender','eyecolor','city') and answer in ('male','female','grey','madrid','amsterdam') group by ID having count(ID)=3; </code></pre> <p>does also work but i would really be cautious with that because.. the questions and answers should stay together and be explicit because.... what if it was a dating service? and <code>male</code> could be an answer for a person's own gender or the gender they want to date and by doing <code>question='gender' and answer in ('male','female')</code> you are specifying exactly what you mean and not assuming that certain information is only a valid answer for one question. </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