Complex query across multiple tables

I'm simplifying my actual case to get the point across quicker, just as a heads up if this see开发者_高级运维ms nonsensical.

I have a table called Candy_Central. Candy_Central has all of the ids for my candy, the price per pound as well and a couple other significant pieces of information that are the same across all Candy records. It also has a field called Candy_Type. Candy_Type's value might be chocolate, hard, gummy, or liquid.

I have another set of tables that are used for the candy information, but their use is determined by candy type. For instance, if I want the rest of the information about my prime chocolate bar, I need to go to Chocolate_Standard to get the rest of the information. If I'm looking up gummy worms with sugar on the outside, I need to go to Gummy_Standard. The reason is that the fields for one set of information are completely different than the fields for the other.

I have one more layer of information, colors. I have Candy that's blue, I have candy that's black, and I have candy that's both colors. The way I'm currently storing this information, I have a table with the fields Candy_Id and Color. To find all of the colors I'd normally just do a query on this table using the color_id field to find the candy I'm interested in. But SQL of course is about finding all of your results. So if I wanted to find all of the gummy info I'd want to do a join or union of some sort. I'm not extremely familiar with this type of query so I'm not sure how to get the result table that I want, which is basically going to be [sorry for the psuedo query] (items from Candy_Main as CM ... items from Gummy as g ... items from color as c ... where cm.candy_id = g.candy_id and cm.candy_id = c.candy_id ). If the gummy's didn't have colors, I'd just want null. I'm fine with getting multiple lines back for each candy_id, but I'd want this as limited as possible just so that post processing (turning the multiple candy lines into one object in my application) took as little time as possible.

Tables:

Candy_Main
    candy_id (primary key, auto increment, not null, etc)
    candy_name (varchar)
    candy_inventor_id (a primary key from some other table, but this is needed for the where clause.
    candy_type (varchar)

Gummy_Standard
    gummy_id (primary, auto)
    candy_id (should match to candy_main)
    softness (varchar)
    outer_coating_type (varchar)
    animal_likeness (varchar)

Gummy_Colors
    gummy_color_id (primary, auto)
    gummy_id (int)
    candy_id (int)
    color (varchar(64))

Gummy_Ingredients
    gummy_ingredient_id (primary, auto)
    gummy_id (int)
    candy_id (int)
    ingredient (varchar(64))

Now, I want to do a query for all entries where candy_inventor_id = 5 AND candy_type = 'gummy'. At the end of the query, I'd want candy_name, candy_type, softness, outer_coating_type, animal_likeness, colors, and ingredients.

Color & ingredients are the only objects that aren't 1 to 1 in this query, so I'd like there to be only as many entries as there are colors and ingredients. Thanks for the help if this is possible. I have a feeling that it'd be easy enough to limit this for just colors or just ingredients, but that it'll be too difficult to do it with both.

try this

SELECT c.candy_name, c.candy_type, s.softness, s.outer_coating_type, 
      s.animal_likeness, 
      GROUP_CONCAT(gc.color), 
      GROUP_CONCAT(gi.ingredient)
FROM candy_main c
LEFT JOIN gummy_standard s on s.candy_id=c.candy_id
LEFT JOIN gummy_colors gc on gc.candy_id = c.candy_id
LEFT JOIN gummy_ingredients gi cl on cl.candy_id = c.candy_id
WHERE c.candy_inventor_id=5 AND c.candy_type='gummy'
GROUP BY gc.color , gi.ingredient

If I understand your DB setup correctly, for each candy, there are many potential gummy_standards (so a hard bear and a chocolate bear might be two gummy standard records linked to one candy record). In that case, the following should work:

SELECT cm.candy_name, cm.candy_type, gs.softness, gs.outer_coating_type, gs.animal_likeness, gc.color, gi.ingredient
FROM Candy_Main cm
    INNER JOIN Gummy_Standard gs ON gs.candy_id = cm.candy_id
    INNER JOIN Gummy_Colors gc ON gc.candy_id = gs.gummy_id
    INNER JOIN Gummy_Ingredients gi ON gi.gummy_id = gs.gummy_id
WHERE candy_inventor_id = 1  -- Of course, this 1 be replaced with the actual inventor's ID

Bear in mind that doing it like this causes duplications to cross multiply, so if a gummy has three colours and 4 ingredients, each ingredient is repeated for all three colours (so 12 lines for just the one gummy). If you don't want this, try concatenating the strings to one line and then splitting the line in code. Don't know what the function in MySQL is, but if concat_ws() works like textcat_all() does in PostgreSQL, then the following should work:

SELECT cm.candy_name, cm.candy_type, gs.softness, gs.outer_coating_type, gs.animal_likeness, CONCAT_WS(', ', gc.color), CONCAT_WS(', ', gi.ingredient)
FROM Candy_Main cm
    INNER JOIN Gummy_Standard gs ON gs.candy_id = cm.candy_id
    INNER JOIN Gummy_Colors gc ON gc.candy_id = gs.gummy_id
    INNER JOIN Gummy_Ingredients gi ON gi.gummy_id = gs.gummy_id
WHERE candy_inventor_id = 1  -- Of course, this 1 be replaced with the actual inventor's ID
GROUP BY cm.candy_name, cm.candy_type, gs.softness, gs.outer_coating_type, gs.animal_likeness

Two tips on the architecture:
1. If my understanding of your tables is correct and ingredients and colors always apply to a gummy standard record, then the candy_id field in Colors and Ingredients is redundant. Better just to connect to Candy through Gummy Standard each time.
2. You might benefit from a higher degree of normalisation, i.e. creating a colors table and an ingredients table and then linking to those, rather than including the string description in your tables as they are. This will make it easier to make changes for all appropriate records, i.e. say you want to change the name of the Blue colour to Blue (Dark) because you're adding Blue (Light), you should only have to update one record rather than all of the Color records saying Blue (or blue, or even blu or blou since misspellings frequently occur when you have to type the same description in every time). So your tables would look like:

Color
    color_id (primary, auto)
    color (varchar(64))

Ingredient
    ingredient_id (primary, auto)
    ingredient (varchar(64))

Gummy_Colors  
    gummy_color_id (primary, auto)  
    gummy_id (int)  
    candy_id (int)  
    color_id (foreign key to Color)  

Gummy_Ingredients  
    gummy_ingredient_id (primary, auto)  
    gummy_id (int)  
    candy_id (int)  
    ingredient_id (foreign key to Ingredient)  

Hope this helps.