Mohammed Kumel Mohammed Kumel - 7 months ago 25
PHP Question

Improper SQL Statement (solved)

Table 1

Questions


ID | Name | Category
1 | xyz | 1
2 | abc | 1
3 | ghj | 2
4 | tyu | 3


Table 2
Data


ID | QuestionID | Category
1 | 2 | 1
2 | 3 | 2


I want results to be all of the questions from
Question
table. WHERE Category = 1

ID | Question | DataID
1 | xyz | NO
2 | abc | YES (data id=1 ie questionid= 2)
3 | ghj | NO (data id=2 ie questionid= 3 but where category = 2 fail so 'NO')
4 | tyu | NO


So i need to display YES if data present else NO.

Answer

You can try LEFT JOIN along with CASE WHEN Statement.

Here's the query:

SELECT
Q.ID,
Q.Name AS 'Question',
CASE WHEN D.QuestionID IS NULL THEN 'NO' ELSE 'YES' END AS DataID
FROM Questions Q LEFT JOIN Data D
ON Q.ID = D.QuestionID
ORDER BY Q.ID;

MySQL JOINS

Demo Here

EDIT:

For your edited requirement

I want results to be all of the questions from Question table. WHERE Category = 1

Query:

SELECT
Q.ID,
Q.Name AS 'Question',
CASE WHEN D.QuestionID IS NULL OR D.category <> 1 THEN 'NO' ELSE 'YES' END AS DataID
FROM Questions Q LEFT JOIN Data D
ON Q.ID = D.QuestionID
ORDER BY Q.ID;

Updated Demo