Re: Design and Question

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