How to get list of elements from 2 table with relationship that contain status true where exist and-CodePudding

consider that i have question table :

    Id    |  name
-------------------
1         | question_1
2         | ...
3         | ...
4         | ...

and response table with relationship

 ID       |  name       |  question_id
------------------------------------
1         | response_1  |     1
2         | ...         |     2
3         | ...         |     4  
4         | ...         |     4

I want guet the list of all question with status column (answerd=true, not answered=false) like that :

question_Id  |  status
-------------------
1            | true
2            | true
3            | false
4            | true

How can i do that with best request ?

CodePudding user response：

Use a LEFT JOIN with a subquery that returns all the answered questions. Then return true if there's a match, false if not.

SELECT q.id AS question_id, IF(r.question_id IS NULL, 'false', 'true') AS status
FROM question AS q
LEFT JOIN (
    SELECT DISTINCT question_id
    FROM response
) AS r