[RESOLVED] Combining SQL statements

[nmadd]

Hi all,
Trying my hand at PHP and that of course means MySQL as well. My first project (a recipe DB for my wife) is working fine but I'm think my current method is not great.

My knowledge of SQL is pretty poor so I currently have the two below SQL statements in use. One to grab the recipe and related information and the other to grab the recipes ingredients. It is currently like this because of the ingredients many-to-many relationship to the recipe and because I'm putting the two different result sets in different tables.

1) I imagine it is bad practice to hit the database twice like this?
2) How should I go about combining these two statements?
-2a) If you could just maybe bump me in the right direction instead of just giving the answer. I'd like to learn.

Please let me know if I can provide more information.

Thanks!

    SELECT RecipeName, Servings, BakeTime, Instructions,
    (
        SELECT Category
        FROM category
        WHERE category.CategoryID = recipe.CategoryID
    ) AS Category,
    (
        SELECT Source
        FROM source
        WHERE source.SourceID = recipe.SourceID
    ) AS Source,
    (
        SELECT Holiday
        FROM holiday
        WHERE holiday.HolidayID = recipe.HolidayID
    ) AS Holiday
    FROM recipe
    WHERE recipe.RecipeID = %d;

    SELECT ingredient.Ingredient, recipeingredient.Quantity, recipeingredient.Comments,
    (
        SELECT Measurement
        FROM measurement
        WHERE measurement.MeasurementID = recipeingredient.MeasurementID
    ) AS Measurement
    FROM ingredient
    JOIN recipeingredient ON
    (
        ingredient.IngredientID = recipeingredient.IngredientID
    )
    WHERE recipeingredient.RecipeID = %d;

[si_the_geek]

It is fine to run it as two separate statements, and is actually better that way.

The reason for this is that you are presumably getting different amounts of rows from each query - the first returns one row (including "Instructions", which I guess is large), and the second returns multiple rows.

If you merge them, you will get all of the data for the first query repeated in every row, along with the data from the second query. This extra data wastes memory and reduces speed - as well as increasing network traffic if your code/database are on different computers.

The only time it might be worthwhile merging them is if the data returned by the first query is small, and/or there are only 1 or 2 rows returned by the second query. Unless you expect this to be the usual case, I wouldn't recommend doing it - what you have already is better for the majority of cases (perhaps even all).


One thing that I don't like about your current method tho is your use of sub-queries for Category etc, you should be using joins instead as you do for recipeingredient (if you have the choice, preferably specify "Inner"/"Left"/etc, to make it easier to read).

[nmadd]

Thanks for the response si. I'm glad that was your answer.

I used the subqueries because they seemed a bit easier to construct; particularly when I had multiple tables involved. If the joins will be more effective, then I'm definitely going to make the change.

[nmadd]

Actually, I take that back. I got myself all scared with joins on a bunch of tables and it actually was easy. Thanks again.

[si_the_geek]

The joins effectively combine the Where and From clauses of the sub-queries, and then you can simply put the fields directly into the Select clause, eg:

    SELECT RecipeName, Servings, BakeTime, Instructions, 
           Category.Category, Source.Source, Holiday.Holiday
    FROM recipe
    INNER JOIN category ON (category.CategoryID = recipe.CategoryID)
    INNER JOIN source ON (source.SourceID = recipe.SourceID)
    INNER JOIN holiday ON (holiday.HolidayID = recipe.HolidayID)
    WHERE recipe.RecipeID = %d;

edit: I took a break while writing this, so took longer than your own reply - but I think it will still be useful for comparison!


Another little bonus this gives you is the ability to use Aliases for the table names, which can make the query shorter and easier to manage. You simply specify the alias in the From or Join clauses, and then use them instead of the table names, eg:

    SELECT R.RecipeName, R.Servings, R.BakeTime, R.Instructions, 
           C.Category, S.Source, H.Holiday
    FROM recipe R
    INNER JOIN category C ON (C.CategoryID = R.CategoryID)
    INNER JOIN source S ON (S.SourceID = R.SourceID)
    INNER JOIN holiday H ON (H.HolidayID = R.HolidayID)
    WHERE R.RecipeID = %d;

(note that I added the table to the first few fields - this reduces the chances of errors, and slightly improves the time the query takes to run).

[nmadd]

Perfect. Thanks for the great post. I have a feeling that you'll be seeing a lot of me in this forum now.