mysql - 带有 case 语句的 SELECT 语句

Question

我有下表给客户:

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int(11)     | NO   | PRI | NULL    | auto_increment |
| first      | varchar(45) | YES  |     | NULL    |                |
| last       | varchar(45) | YES  |     | NULL    |                |
| password   | varchar(45) | NO   |     | NULL    |                |
| contact_id | int(11)     | NO   | PRI | NULL    |                |
| address_id | int(11)     | NO   | PRI | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

以及约会的以下结构:

+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| id          | int(11)    | NO   | PRI | NULL    | auto_increment |
| time        | datetime   | NO   |     | NULL    |                |
| cancelled   | tinyint(1) | YES  |     | 0       |                |
| confirmed   | tinyint(1) | YES  |     | 0       |                |
| customer_id | int(11)    | NO   | PRI | NULL    |                |
+-------------+------------+------+-----+---------+----------------+

我想使用单个查询来获取客户信息，如果他们有约会信息，那么它会查询它，否则不会。

我正在尝试使用以下内容:

CASE
    WHEN (SELECT count(a.id) FROM appointment 
          INNER JOIN customer c ON a.customer_id = c.id)
        THEN (SELECT c.first, c.last, c.id, a.id FROM appointent
              INNER JOIN customer c ON a.customer_id = c.id)
    ELSE
        (SELECT c.first, c.last, c.id FROM customer)
END;

你有什么建议吗？

Answer 1

怎么样

SELECT * FROM Customer c LEFT JOIN Appointment a ON a.CustomerId = c.Id