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?
