CODEWITHSUNDEEP
mysqlsql
Richard
Richard

Reputation: 1052

Many JOINs in a long SQL call

There are 4 tables, each is a tuple ID(INT),name(VARCHAR) (the ID is autoincrementing). There is a table for Meal (Cheeseburger, Hamburger, Meat Feast, BLT, Tomato Soup), Class (Burger, Pizza, Sandwich, Soup), Characteristics (Spicy, Vegtarian, Gluten Free), and Sides(Salad, Fries, Bread).

Three more tables exist to make the tuples Meal.ID,Class.ID, Meal.ID,Characteristics.ID, Meal.ID,Sides.ID so that a Meal can be in more than one class, have more than one characteristic, and come with more than one side, and vice versa.

I had the following lazy loading just fine, but we all know that's not good. What I want is the name of the meal, it's characteristics, sides and alternative meals from the same class.

This call worked just fine:

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

But when I extended it out to this, I got nothing back:

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

Any ideas how to fix the call, or how to have a better structured schema? Or is a degree of lazy loading the best way to go here (:P)?!

Upvotes: 1

Views: 87

Answers (1)

Philip Couling
Philip Couling

Reputation: 14903

There are three possible things that you would like here. I've written them out as a demonstration.

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);

This is the same as you used:
(a LEFT JOIN b) JOIN 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

This would be using just left joins:
(a LEFT JOIN b) LEFT JOIN 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 |   |

I think this is what you were aiming for: a LEFT JOIN (b JOIN 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 |   |   |   |

Hope this helps

Upvotes: 1

Related Questions