mysql - mysql 连接困难(空值未显示)

Question

所以我有 6 个表，全部有 2 列。 3 个包含我正在使用的数据，另外 3 个仅指定它们的关联方式。

因此，对于这个示例数据:

central_item

id   name
-----------------
1    Chicken
2    Shrimp


cooking_method

id   name
-----------------
1    Bake
2    Fry


style

id   name
-----------------
1    Casserole
2    Pie


central_item_cooking_method

central_item  cooking_method
------------------------------
1             1
1             2
2             1
2             2


central_item_style

central_item  style
------------------------------
1             1
1             2
2             1


cooking_method_style

cooking_method  style
------------------------------
1               1
2               2

我正在尝试得到这个:

central_item_name  style_name  cooking_method_name
----------------------------------------------------
Chicken            null        Bake
Chicken            null        Fry
Chicken            Casserole   null
Chicken            Casserole   Bake
Chicken            Pie         null
Chicken            Pie         Bake
Shrimp             null        Bake
Shrimp             null        Fry
Shrimp             Casserole   null
Shrimp             Casserole   Bake

这是我一直在尝试的查询。注释解释了每个部分应该做什么。当我运行它时，查询丢失了很多结果，我希望只看到 1 列为空。

SELECT
#name these something better so they don't all get returned as just 'name'
central_item.name as `central_item_name`, style.name as `style_name`, cooking_method.name as `cooking_method_name`
#we need a central item no matter what so start here
FROM central_item
#get styles for items (optional)
LEFT JOIN central_item_style ON central_item_style.style = central_item.id OR central_item_style.style IS NULL
#get names for any matching styles
LEFT JOIN style ON style.id = central_item_style.style OR style.id IS NULL
#get cooking methods for items (optional)
LEFT JOIN central_item_cooking_method ON central_item_cooking_method.central_item = central_item.id OR central_item_cooking_method.central_item IS NULL
#get names for cooking methods
LEFT JOIN cooking_method ON cooking_method.id = central_item_cooking_method.cooking_method OR cooking_method.id IS NULL
#for the matching item cooking methods check which styles also match the cooking method.  For item styles check for matching cooking methods.
LEFT JOIN cooking_method_style ON cooking_method_style.style = central_item_style.style OR cooking_method_style.cooking_method = central_item_cooking_method.cooking_method

WHERE
#make sure we have at least one of these
(style.id IS NOT NULL OR cooking_method.id IS NOT NULL) AND
#cooking method or style can be null but we have both they need to be compatible with each other
(cooking_method.id IS NULL OR central_item_style.style = cooking_method_style.style) AND
(style.id IS NULL OR central_item_cooking_method.cooking_method = cooking_method_style.cooking_method)

#Remove duplicates
GROUP BY central_item.name, style.name, cooking_method.name
ORDER BY central_item.name, style.name, cooking_method.name

Answer 1

考虑这个示例(但请注意，它返回的结果集略有不同)...

 DROP TABLE IF EXISTS ingredients;
 CREATE TABLE ingredients
 (ingredient_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,ingredient VARCHAR(12) NOT NULL
 );

 INSERT INTO ingredients VALUES (1    ,'Chicken'),(2    ,'Shrimp');

 DROP TABLE IF EXISTS methods;
 CREATE TABLE methods
 (method_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,method VARCHAR(12) NOT NULL
 );

 INSERT INTO methods VALUES
 (1    ,'Bake'),
 (2    ,'Fry');

 DROP TABLE IF EXISTS styles;
 CREATE TABLE styles
 (style_id   INT NOT NULL
 ,style VARCHAR(12) NOT NULL
 );

 INSERT INTO styles VALUES
 (1    ,'Casserole'),(2    ,'Pie');

 DROP TABLE IF EXISTS ingredient_method;
 CREATE TABLE ingredient_method
 (ingredient_id INT NOT NULL,method_id INT NOT NULL,PRIMARY KEY(ingredient_id,method_id));

 INSERT INTO ingredient_method VALUES
 (1             ,1),(1             ,2),(2             ,1),(2             ,2);

 DROP TABLE IF EXISTS ingredient_style;
 CREATE TABLE ingredient_style
 (ingredient_id INT NOT NULL,style_id INT NOT NULL,PRIMARY KEY(ingredient_id,style_id));

 INSERT INTO ingredient_style VALUES
 (1             ,1),(1             ,2),(2             ,1);


 DROP TABLE IF EXISTS method_style;

 CREATE TABLE method_style
 (method_id INT NOT NULL,style_id INT NOT NULL,PRIMARY KEY(method_id,style_id));

 INSERT INTO method_style VALUES(1               ,1),(2               ,2);


 SELECT i.ingredient
      , s.style
      , m.method
   FROM ingredients i
   LEFT
   JOIN ingredient_style si
     ON si.ingredient_id = i.ingredient_id
   LEFT
   JOIN ingredient_method im
     ON im.ingredient_id = i.ingredient_id   
   LEFT
   JOIN styles s
     ON s.style_id = si.style_id
   LEFT
   JOIN method_style ms
     ON ms.style_id = si.style_id
   LEFT
   JOIN methods m 
     ON m.method_id IN(ms.method_id,m.method_id = im.method_id);

+------------+-----------+--------+
| ingredient | style     | method |
+------------+-----------+--------+
| Chicken    | Casserole | Bake   |
| Chicken    | Casserole | Bake   |
| Chicken    | Casserole | Fry    |
| Chicken    | Pie       | Bake   |
| Chicken    | Pie       | Fry    |
| Chicken    | Pie       | Fry    |
| Shrimp     | Casserole | Bake   |
| Shrimp     | Casserole | Bake   |
| Shrimp     | Casserole | Fry    |
+------------+-----------+--------+

http://sqlfiddle.com/#!2/b0127/1