mysql - 长 SQL 调用中的许多 JOIN

Question

有 4 个表，每个表是一个元组 ID(INT),name(VARCHAR)(ID 是自动递增的)。有一张餐 table (芝士汉堡、汉堡、肉宴、BLT、番茄汤)、类别(汉堡、披萨、三明治、汤)、特色(辣、素食、无麸质)和配菜(沙拉、薯条、面包) .

另外还有三个表来构成元组 Meal.ID,Class.ID、Meal.ID,Characteristics.ID、Meal.ID,Sides。 ID 这样一顿饭就可以属于多个类别，具有多个特征，并且有多个配菜，反之亦然。

我有下面的延迟加载就好了，但我们都知道那不好。我想要的是这顿饭的名称、特色、配菜和同一类的替代餐。

这个调用工作得很好:

SELECT Group_concat(DISTINCT m2.meal_name SEPARATOR ',')          AS alternates,
       Group_concat(DISTINCT c.characteristic_desc SEPARATOR ',') AS
       characteristics
FROM   meal AS M
       INNER JOIN mealclass AS MC
               ON M.meal_id = MC.meal_id
       LEFT JOIN (SELECT meal_id,
                         class_id
                  FROM   drugingredient) AS MC2
              ON MC2.class_id = MC.class_id
       LEFT JOIN meals AS M2
              ON MC2.meal_id = M2.meal_id
       LEFT JOIN mealchar AS MCh
              ON MCh.meal_id = M.meal_id
       INNER JOIN characterisics AS C
               ON C.characteristic_id = MCh.characteristic_id
WHERE  M.meal_id = :meal_id
       AND M.meal_id <> M2.meal_id  

但是当我把它扩展到这个时，我什么也没得到:

SELECT Group_concat(DISTINCT m2.meal_name SEPARATOR ',')          AS alternates,
       Group_concat(DISTINCT c.characteristic_desc SEPARATOR ',') AS
       characteristics,
       Group_concat(DISTINCT s.sides_desc SEPARATOR ',')          AS side_orders
FROM   meal AS M
       INNER JOIN mealclass AS MC
               ON M.meal_id = MC.meal_id
       LEFT JOIN (SELECT meal_id,
                         class_id
                  FROM   drugingredient) AS MC2
              ON MC2.class_id = MC.class_id
       LEFT JOIN meals AS M2
              ON MC2.meal_id = M2.meal_id
       LEFT JOIN mealchar AS MCh
              ON MCh.meal_id = M.meal_id
       INNER JOIN characterisics AS C
               ON C.characteristic_id = MCh.characteristic_id
       INNER JOIN mealsides AS MS
               ON M.meal_id = MS.meal_id
       INNER JOIN sides AS S
               ON S.sides_id = MS.sides_id
WHERE  M.meal_id = :meal_id
       AND M.meal_id <> M2.meal_id 

关于如何修复调用或如何拥有更好的结构化模式有什么想法吗？或者一定程度的延迟加载是最好的方式 (:P)？!

Answer 1

您在这里可能会想要三种东西。我已经将它们写出来作为演示。

create table a (x integer, y integer);
create table b (v integer, w integer);
create table c (t integer, u integer);
insert into a values (1,2);
insert into a values (3,4);
insert into a values (5,6);
insert into b values (1,2);
insert into b values (5,6);
insert into c values (1,2);
insert into c values (3,4);

这与您使用的相同:
(a 左连接 b) 连接 c

select * from a left join b on a.x = b.v join c on b.v = c.t;
 x | y | v | w | t | u
---+---+---+---+---+---
 1 | 2 | 1 | 2 | 1 | 2

这将只使用左连接:
(a 左连接 b) 左连接 c

select * from a left join b on a.x = b.v left join c on b.v = c.t;
 x | y | v | w | t | u
---+---+---+---+---+---
 1 | 2 | 1 | 2 | 1 | 2
 3 | 4 |   |   |   |
 5 | 6 | 5 | 6 |   |

我认为这就是您的目标: a 左连接(b 连接 c)

select * from a left join (b join c on b.v = c.t) on a.x = b.v;
 x | y | v | w | t | u
---+---+---+---+---+---
 1 | 2 | 1 | 2 | 1 | 2
 3 | 4 |   |   |   |
 5 | 6 |   |   |   |

希望对你有帮助