依赖于两个表的记录的减法

Question

表1-测试题

| ID |       QUESTION | ANSWER | STATUS |
-----------------------------------------
|  1 | first question |  demo1 |   done |
|  2 |    second que. |  demo2 |   done |
|  3 |      Third que |  demo3 |   done |
|  4 |     Fourth que |  demo4 |   done |

表2- Playedby

| ID | PLAY_ID | QUEST_ID | PLAYER_ANSWER |
-------------------------------------------
|  1 |       5 |        1 |             c |
|  2 |       4 |        1 |             a |
|  3 |       5 |        2 |             d |
|  4 |       4 |        2 |             b |
|  5 |       4 |        3 |             b |
|  6 |       4 |        4 |             c |
|  7 |       1 |        1 |             a |
|  8 |       3 |        1 |             c |
|  9 |       1 |        3 |             d |

我想拿到那些问题谁不是由用户发挥。例如，以用户1为例。

我想获取用户1没有玩过的问题，我该怎么做呢？

Answer 1

像这样尝试LEFT JOIN：

   SELECT q.*
     FROM testquestion q
LEFT JOIN Playedby p
       ON q.id = p.quest_id
      AND play_id = 1
    WHERE p.play_ID IS NULL

输出(用于Play_id = 1)

╔════╦═════════════╦════════╦════════╗
║ ID ║  QUESTION   ║ ANSWER ║ STATUS ║
╠════╬═════════════╬════════╬════════╣
║  2 ║ second que. ║ demo2  ║ done   ║
║  4 ║ Fourth que  ║ demo4  ║ done   ║
╚════╩═════════════╩════════╩════════╝

See this SQLFiddle