On 10 jul 2008, at 14.50, PostgreSQL Admin wrote:
> How do I combine the two in a query?
If you're looking for recipes that match *either* criterion (season
*or* diet), you could add the two subqueries generating the ids using
UNION or UNION ALL:
SELECT title FROM recipes WHERE id IN (
SELECT recipe_id FROM recipes_season WHERE season IN ('P', 'W')
UNION [ALL]
SELECT recipe_id FROM recipes_diet WHERE diet IN ('P')
);
or, you could use joins:
SELECT title FROM recipes r
LEFT JOIN recipes_season rs ON r.id=rs.recipe_id
LEFT JOIN recipes_diet rd ON r.id=rd.recipe_id
WHERE rs.season IN ('P', 'W') OR rd.diet IN ('P');
If, on the other hand, you're looking for recipes that match *both*
criteria, use:
SELECT title FROM recipes WHERE id IN (SELECT recipe_id FROM
recipes_season WHERE season IN ('P', 'W'))
AND id IN (SELECT recipe_id FROM recipes_diet WHERE diet IN ('P'));
or:
SELECT title FROM recipes r
INNER JOIN recipes_season rs ON r.id=rs.recipe_id
INNER JOIN recipes_diet rd ON r.id=rd.recipe_id
WHERE rs.season IN ('P', 'W') AND rd.diet IN ('P');
The optimal execution plan will be dependent on the size and
distribution of your data, so you should test the queries with real
data.
Sincerely,
Niklas Johansson
