Re: SQL or table design optimation

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.