Обсуждение: Design and Question

Hi,

I have BOTH a sql AND db design question.

I'm creating a cookbook DB with have broken the table into this:

RECIPE TABLE

Column | Type | Modifiers
-------------------+--------------------------+------------------------------------------------------
id | integer | not null default nextval('recipes_id_seq'::regclass)
title | character varying(150) | not null
description | text | not null
servings | integer |
instruction | text | not null
photo | character varying(100) | not null
difficulty | integer |
cuisine | smallint |
course | smallint |
season | smallint |
dietary | smallint |
technique | smallint |
published_date | timestamp with time zone | not null
publishing_rights | boolean | not null
credits | character varying(100) | not null
approved | boolean | default false
cooktime | integer |
preptime | integer |

and this:

RECIPE DIET INFO TABLE
Column | Type | Modifiers
-----------+----------------------+-----------------------------------------------------------
id | integer | not null default nextval('recipes_diet_id_seq'::regclass)
recipe_id | integer | not null
diet | character varying(1) |

RECIPE SEASON TABLE
Column | Type | Modifiers
-----------+----------------------+-------------------------------------------------------------
id | integer | not null default nextval('recipes_season_id_seq'::regclass)
recipe_id | integer | not null
season | character varying(1) |

I can perform is query ->

select title from recipes where id in (select recipe_id from 
recipes_season where season in ('P', 'W'));

title
---------------------------------------
ButterFlied Chicken Fillets with Lime
Balsamic Vinegar Chicken with Beans

(2 rows)

select title from recipes where id in (select recipe_id from 
recipes_diet where diet in ('P'));

title
---------------------------------------
ButterFlied Chicken Fillets with Lime

How do I combine the two in a query?

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