Skip site navigation (1) Skip section navigation (2)

Design and Question

From: PostgreSQL Admin <postgres(at)productivitymedia(dot)com>
To: PostgreSQL SQL List <pgsql-sql(at)postgresql(dot)org>
Subject: Design and Question
Date: 2008-07-10 12:50:23
Message-ID: 4876058F.4000702@productivitymedia.com (view raw or flat)
Thread:
Lists: pgsql-sql
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?

Responses

pgsql-sql by date

Next:From: Anoop GDate: 2008-07-10 12:55:38
Subject: Problem in dynamic query execution in plpgsql
Previous:From: aldyDate: 2008-07-10 08:16:53
Subject: Re: i can't connect after some periode

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group