[RESOLVED] Combining SQL statements

[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).