Databases
StackOverflow2013
Postsdbo
POSQL: conditioning multiple rows on an inner joined table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL: conditioning multiple rows on an inner joined table
    primarykey
    data
    text
    <p>I am needing some help with SQL syntax.</p> <p>Say I have a members table, a questions table and an answers table, basics of the tables are as follows:</p> <p>Members table:</p> <pre><code>memberId: primary key </code></pre> <p>Questions table:</p> <pre><code>questionId: primary key, questionText:varchar </code></pre> <p>Answers table:</p> <pre><code>answerId: primary key, questionId: int (relating to the row of the question in the questions table) memberId: int (relating to the row of the member in the members table) answerValue:varchar </code></pre> <p>The tables will have more columns but for the purpose of this problem these should suffice.</p> <p>Now In some instances I will want to run a query that will return a distinct list of member ids where the members answers to a list of questions match a certain value.</p> <p>For instance:</p> <p>Say there is a question in the questions table (row 1) with the question text: "Do you like cats or dogs?":</p> <pre><code> questionId questionText ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 Do you like cats or dogs? </code></pre> <p>Then there are 10 members in the members table from id 1 to 10, and corresponding answers in the answers table:</p> <pre><code> answerid questionId memberId answerId --------------------------------------------------------------------------------------- 1 1 1 cats 2 1 2 both 3 1 3 cats 4 1 4 cats 5 1 5 cats 6 1 6 dogs 7 1 7 dogs 8 1 8 dogs 9 1 9 dogs 10 1 10 both </code></pre> <p>It would be simple enough to query with this for example, those who answered dogs:</p> <pre><code>SELECT DISTINCT memberId FROM members INNER JOIN answers ON members.memberId = answers.answerId WHERE answers.questionId = 1 AND answers.answerValue = 'dogs' </code></pre> <p>This would return:</p> <pre><code> memberId --------------- 6 7 8 9 </code></pre> <p>But what if I were to add another question to the questions table:</p> <pre><code> questionId questionText ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 Do you like cats or dogs? 2 What is your favourite color? </code></pre> <p>And the answers table was updated as follows:</p> <pre><code> answerid questionId memberId answerId --------------------------------------------------------------------------------------- 1 1 1 cats 2 1 2 both 3 1 3 cats 4 1 4 cats 5 1 5 cats 6 1 6 dogs 7 1 7 dogs 8 1 8 dogs 9 1 9 dogs 10 1 10 both 11 2 1 blue 12 2 2 red 13 2 3 green 14 2 5 green 15 2 4 black 16 2 6 violet 17 2 7 pink 18 2 8 green 19 2 9 red 20 2 10 yellow </code></pre> <p>How would I query for multiple questions? </p> <p>What I am looking for is a set syntax that would work for querying a list of distinct members who have answered specific answers to any number of questions, for instance:</p> <p>The query for members who like ((dogs AND red) OR (cats AND green)) should return:</p> <pre><code> memberId ----------------- 9 3 5 </code></pre> <p>There could be any combination of answers. The essence of it is how to I query against multiple values for an inner joined table?</p> <p>Thanks for any help anyone can give, sorry if its a little confusing.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USNeaox
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL: conditioning multiple rows on an inner joined table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL: conditioning multiple rows on an inner joined table
      1. USArthur
      1. VTFavorite
    1. COIts kind of hard to explain the real world implementation of this but I've give it a try basically I have designed an email engine which takes a list of conditions like (animal = cat and color = green (very simplified of course)) and queries the database for members matching those conditions by generating an sql query it then sends out emails to returned ids.
      singulars
      1. POSQL: conditioning multiple rows on an inner joined table
      1. USNeaox
    2. COIn terms of the example given I have tried SELECT DISTINCT memberId FROM members INNER JOIN answers ON members.memberId = answers.answerId WHERE (((answers.questionId = 1 AND answers.answerValue = 'dogs') AND (answers.questionId = 2 AND answers.answerValue = 'red')) OR ((answers.questionId = 1 AND answers.answerValue = 'cats') AND (answers.questionId = 2 AND answers.answerValue = 'green')))
      singulars
      1. POSQL: conditioning multiple rows on an inner joined table
      1. USNeaox
    3. COI cannot discern what you are asking here. Your join query in your comment above doesn't even make sense. Why would you join memberid=answerid?
      singulars
      1. POSQL: conditioning multiple rows on an inner joined table
      1. USkenny
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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