From: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
---|---|
To: | Verena Ruff <lists(at)triosolutions(dot)at> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: SQL or table design optimation |
Date: | 2006-05-30 16:25:30 |
Message-ID: | 20060530092342.U55216@megazone.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Tue, 30 May 2006, Verena Ruff wrote:
> Hi,
>
> now I've got a question about table and/or query design. It's about
> having persons and contacts. Since every person should have as many
> contacs as neccessary, I created two separate tables:
>
> CREATE TABLE person (
> pers_id serial PRIMARY KEY,
> name character varying(50)
> );
>
> CREATE TABLE contact (
> id serial PRIMARY KEY,
> kind character varying(20),
> type small int,
> value character varying(75),
> pers_id integer
> );
>
> where kind describes the kind of the contact: mobile, email, www, ...,
> type says if this contact should be used as private or business contact
> and whether or not this is the standard contact for this person for this
> medium. value is the telephone number, email adress or whatever, pers_id
> is a foreign key to the person table.
>
> Now I'd like to have a query which includes some fields of person
> combined with the standard business contact for mobile and email, where
> it might happen that some persons do not have such a contact, but they
> should be included in this list anyway.
>
> I tried it with the following:
>
> SELECT DISTINCT name, email, mobile
> FROM person AS p
> LEFT OUTER JOIN
> (
> SELECT person.pers_id, value as email
> FROM person
> INNER JOIN contact ON person.pers_id=contact.pers_id
> WHERE kind='email' AND type=1
> ) AS q1 ON p.pers_id=q1.pers_id
I think a construct like
person AS p left outer join contact on
(p.pers_id = contact.pers_id and kind='email' and type=1)
may give the right results without another scan of person.
From | Date | Subject | |
---|---|---|---|
Next Message | Terry Lee Tucker | 2006-05-30 21:20:18 | Re: trigger help |
Previous Message | Verena Ruff | 2006-05-30 15:53:52 | Re: SQL or table design optimation |