Tipo94 Tipo94 - 5 months ago 11
SQL Question

Conditionnal select with MYSQL, performance issue

I am trying to find the best way to perform a onditional query on a table that is looking like this:


primaryKey question answer clientId
1 are you fine? yes Bob
2 are you fine? no Tom
3 what is your name Bob Bob
4 What is your name Tom Tom


I have to select in this table the primary key of all answers to the question "What is your name" but only for those that answered "yes" to "are you fine" (ie I want to retrieve 3)

I have try a subquery in a where in clause like so:


SELECT primaryKey
FROM (`questionTable`)
WHERE clientId IN(
SELECT clientId FROM `questionTable`
WHERE (question = "are you fine" AND answer = "yes")
)
AND question = "What is your name"


This query actually never finish, it doesn't throw any error, it just runs forever.

The only solution I found involve selecting using the clientId instead of the primary key but that is too slow and I would like to find a way to retrieve the primaryKey in one query.

Answer

Use a self join the table:

SELECT t1.primaryKey 
FROM `questionTable` t1
INNER JOIN `questionTable` t2
    ON t1.clientId = t2.clientId
WHERE t2.question = "are you fine?" AND
      t2.answer = "yes" AND
      t1.question = "What is your name"

SQLFiddle