php - MySQL Join 返回左表中的所有数据以及右表中找到的所有数据

Question

脑子不好。有些东西没有点击，我知道这可能很简单。我正在尽力避免子查询，但这可能是不可避免的。

左表 [cards_types] 中有 11 条记录，右表 [users_cards] 中有 1 到 11 条记录。我需要返回左表中的所有记录以及右表中找到的所有记录。右表的唯一注意事项是执行一些 IF/ELSE 语句以在 card_types 中返回 0 值。id 在 users_cards 中找不到。此外，cards_types.id => users_cards.type_id 有一个外键约束(如果重要的话) .

查询

SELECT
  t.id,
  t.slug,
  t.label AS type_label,
  t.points AS point_value,
  IF (c.mtg_id IS NULL, 0, c.mtg_id) AS mtg_id,
  IF (c.label IS NULL, 0, c.label ) AS card_label,
  IF (uc.points IS NULL, 0, uc.points ) AS card_score
FROM cards_types t
JOIN users_cards uc
  ON uc.type_id = t.id
JOIN cards c
  ON c.id = uc.card_id
WHERE uc.user_id = 1
  AND uc.season_id = 1
ORDER BY t.priority ASC

Answer 1

您当前正在使用 INNER JOIN，将其更改为 LEFT JOIN。我还将您的 WHERE 子句过滤器移动到 JOIN，因此您将从 cards_type 返回所有行。如果您将过滤器留在 WHERE 子句中，那么它将像 INNER JOIN 一样工作:

SELECT
  t.id,
  t.slug,
  t.label AS type_label,
  t.points AS point_value,
  COALESCE(c.mtg_id, 0) AS mtg_id,
  COALESCE(c.label, 0) AS card_label,
  COALESCE(uc.points, 0) AS card_score
FROM cards_types t
LEFT JOIN users_cards uc
  ON uc.type_id = t.id
  AND uc.user_id = 1  -- < -- move the where filters here
  AND uc.season_id = 1
LEFT JOIN cards c
  ON c.id = uc.card_id
ORDER BY t.priority ASC