php - 不正确的 SQL 语句

Question

表 1 问题

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

表 2 数据

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

我希望结果是 Question 表中的所有问题。其中类别 = 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

所以如果数据存在，我需要显示 YES，否则显示 NO。

Answer 1

您可以尝试 LEFT JOIN 和 CASE WHEN 语句。

这是查询:

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

编辑:

针对您编辑的要求

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

查询:

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