mysql - 如何连接 2 个 MySQL 表以确定连接的表是否匹配

Question

我有 2 个 MySQL 表(国家)和(预订)

我想列出来自国家的每一行，并简单地指示预订是否匹配。

在每种情况下:

• 预订 将有 1 个匹配行，或者
• 预订 将有许多匹配的行，或者
• 预订将没有匹配的行

所以我想从连接中得到的只是知道是否与reservations匹配。没有别的了。

这是我的查询语句:

SELECT country.countryID, reservation.citizen
FROM countries AS country
LEFT JOIN (
  SELECT reservationID
  FROM reservations
  LIMIT 1
) AS reservation ON reservation.citizen = country.countryID
ORDER BY reservation.citizen

失败，“on 子句”中存在未知列“reservation.citizen”

结论:JOIN 无法解决问题。

Answer 1

问题出在子选择中。请注意，您尚未将现场公民包含在预订中。

SELECT country.countryID, reservation.citizen
FROM countries AS country
LEFT JOIN (
  SELECT reservationID, citizen
  FROM reservations
  LIMIT 1
) AS reservation ON reservation.citizen = country.countryID
ORDER BY reservation.citizen

另一方面，你为什么要进行子选择？为什么有限制？只查询以下内容不是更好吗？

SELECT 
    country.countryID, 
    count(reservation.reservationID)
FROM 
    countries AS country
    LEFT JOIN reservations AS reservation 
    ON country.countryID = reservation.citizen
GROUP BY
    country.countryID
HAVING 
    count(reservation.reservationID) > 0
ORDER BY 
    reservation.citizen
;