mysql - mysql连接三张表

Question

我有如下三个表格。

Courses:
Id Name
1  A
2  B
3  C
4  D

Prices:
Id CId Price
1   1   200
2   2   150
3   3   500
4   4   300

Payment:
Id  CId
1    1
2    4

如果存在类(class)记录的付款记录，则价格应为-1。我想得到如下结果:

Id Name Price
1   A    -1
2   B    150
3   C    500
4   D     -1

如何查询mysql？

Answer 1

试试这个

SELECT c.Id, c.name, 
CASE WHEN pa.id IS NOT NULL THEN -1 ELSE pr.price
END as Price
FROM Courses c
LEFT JOIN Prices pr 
ON c.Id = pr.CId
LEFT JOIN Payment pa
ON c.id = pa.CId;

或

SELECT c.Id, c.name, 
CASE WHEN pa.id IS NULL THEN pr.price ELSE -1 
END as Price
FROM Courses c
LEFT JOIN Prices pr 
ON c.Id = pr.CId
LEFT JOIN Payment pa
ON c.id = pa.CId;