CODEWITHSUNDEEP
phpmysql
IRONLORD
IRONLORD

Reputation: 163

Sub-query doesn't work

Here's my schema:


    Table "Questoes";
    +----------+--------------+------+-----+---------+----------------+
    | Field    | Type         | Null | Key | Default | Extra          |
    +----------+--------------+------+-----+---------+----------------+
    | id       | int(11)      | NO   | PRI | NULL    | auto_increment |
    | id_quest | int(11)      | NO   |     | NULL    |                |
    | questao  | varchar(255) | NO   |     | NULL    |                |
    | nivel    | int(11)      | NO   |     | NULL    |                |
    | tipo     | varchar(255) | NO   |     | NULL    |                |
    +----------+--------------+------+-----+---------+----------------+

    Table "Respostas";

    +----------+---------------+------+-----+---------+----------------+
    | Field    | Type          | Null | Key | Default | Extra          |
    +----------+---------------+------+-----+---------+----------------+
    | id       | int(11)       | NO   | PRI | NULL    | auto_increment |
    | id_quest | int(11)       | NO   |     | NULL    |                |
    | resposta | varchar(255)  | NO   |     | NULL    |                |
    | r_valido | enum('0','1') | NO   |     | NULL    |                |
    +----------+---------------+------+-----+---------+----------------+

My query is:

SELECT q.questao, r.resposta 
FROM questoes q, respostas r 
WHERE q.id_quest IN (19,20,21) 
    AND q.id_quest=r.id_quest 
    AND r.r_valido = ( SELECT resposta FROM respostas WHERE r_valido= 1 )

What I need is the field questao from table Questoes and the field resposta from table respostas where field r_valido = 1. The field resposta have 4 results, and only one is valid, in other words, where the field r_valido = 1.

Upvotes: 1

Views: 97

Answers (2)

Orel Eraki
Orel Eraki

Reputation: 12196

I didn't understood you completely but i think this is what you looking for:

SELECT q.questao, r.resposta
FROM questoes as q
INNER JOIN respostas as r
    ON q.id_quest=r.id_quest 
WHERE
    q.id_quest IN (19,20,21) AND
    r.r_valido = '1'

Upvotes: 1

hynner
hynner

Reputation: 1352

Your query should look like this:

SELECT q.questao, r.resposta FROM questoes AS q JOIN respostas AS r ON r.id_quest = q.id_quest WHERE q.id_quest IN (19,20,21)  AND r.r_valido = "1"

Also I found out what is causing that weird error when you use 1 instead of "1" in the query:

We strongly recommend that you do not use numbers as enumeration values, because it does not save on storage over the appropriate TINYINT or SMALLINT type, and it is easy to mix up the strings and the underlying number values (which might not be the same) if you quote the ENUM values incorrectly

Upvotes: 1

Related Questions