我的数据库中有三个表,名为:questions
、answerquestion
、odds
第一个(问题)有一个问题列表
+=========================+
| ID | question |
+=========================+
| 1 | Bla Bla |
| 2 | question |
| 3 | Other Question |
+-------------------------+
第二个(AnswerQuestion)有一个特定问题用户的答案
+==================================================+
| ID | idQuestion | idOdd | idUser | isCorrect |
+==================================================+
| 1 | 4 | 2 | 5 | 0 |
| 2 | 3 | 1 | 1 | 1 |
| 3 | 1 | 3 | 10 | 0 |
+--------------------------------------------------+
第三个(赔率)与正确的有问题的赔率
+============================================+
| ID | odd | idQuestion | isCorrect |
+============================================+
| 1 | One | 1 | 0 |
| 2 | Two | 1 | 1 |
| 3 | Three | 1 | 0 |
+--------------------------------------------+
因此,为了获得特定用户的答案,我使用了这个查询:
SELECT * FROM answerquestion a, question q, odds o
WHERE a.IdUser = :id AND a.IdOdd = o.ID AND a.IdQuestion = q.ID
这很好,但如果用户的答案是假的,我想得到正确的奇数为此,我在SQL上使用IF
语句,查询为:
SET SQL_BIG_SELECTS=1;
SELECT DISTINCT IF(o.isCorrect = 0, o.Corr.TheCorr, o.odd), q.Question
FROM answerquestion a, odds o, question q,
( SELECT o.odd AS TheCorr FROM odds o, question q WHERE o.isCorrect = 1 AND o.IdQuestion = q.ID) AS Corr
WHERE a.IdUser = 5
AND q.ID = a.idQuestion
AND a.IdOdd = o.ID
这个查询的问题是答案是假的,它给我所有问题的正确答案,并复制问题
示例:
Question ID 4 'Bla Bla' | Correct odd of Question ID 1
Question ID 4 'Bla Bla' | Correct odd of Question ID 2
Question ID 4 'Bla Bla' | Correct odd of Question ID 3
Question ID 4 'Bla Bla' | Correct odd of Question ID 4
Question ID 4 'Bla Bla' | Correct odd of Question ID 5
我想如果问题ID 4的答案,我需要得到正确的奇数
Question ID 4 'Bla Bla' | Correct odd of Question ID 4
有什么帮助吗?
需要使用正确的JOIN。不确定应该选择哪些列,但这很容易修复。
SELECT q.id, q.question, o.odd as correct, o2.odd as user_odd
FROM question q
JOIN AnswerQuestion a ON a.idQuestion = q.id
JOIN Odds o ON o.questionID = q.id AND o.isCorrect = 1
JOIN Odds o2 ON o2.id = a.idOdd
WHERE a.idUser = 5