Thanks for you response Nabil, I'm not sure if it solves my problem as
there may be multiple records in the "bar" table, but I'll work through
it.
Thanks again.
Kevin
On Wednesday, April 7, 2004, at 04:13 PM, Nabil Sayegh wrote:
> Kevin Lohka wrote:
>
>> 1) If the address.people_id field matches the person, use the city
>> and province values,
>> 2) If there is no address record with a matching people_id then use
>> the default 0 address record values.
>> 3) If there is no address record with a matching people_id or the
>> default 0 then fill address.city, address.province with null values
>> I'd only like to have one record returned for each person.
>
> Without looking in detail I think you might find the following usefull:
>
> Consider the following example tables:
>
> CREATE TEMP TABLE foo (foo_id int PRIMARY KEY, foo text);
> INSERT INTO foo VALUES (1, 'one');
> INSERT INTO foo VALUES (2, 'two');
> INSERT INTO foo VALUES (3, 'three');
>
> CREATE TEMP TABLE bar (foo_id int REFERENCES foo, bar text);
> INSERT INTO bar VALUES (1, 'eins');
> INSERT INTO bar VALUES (2, 'zwei');
>
> -----------------------------------------------------------
> "LEFT OUTER JOIN"
>
> SELECT * FROM foo LEFT OUTER JOIN bar USING (foo_id);
>
> Row 3 will be filled with NULLs as it doesnt occur in bar.
> ------------------------------------------------------------
> "COALESCE"
>
> SELECT coalesce(bar, 'This comes instead of NULL') FROM foo LEFT OUTER
> JOIN bar USING (foo_id);
>
> Whenever bar.bar is NULL it will be replaced by the given value.
> ------------------------------------------------------------
>
> HTH
> --
> e-Trolley Sayegh & John, Nabil Sayegh
> Tel.: 0700 etrolley /// 0700 38765539
> Fax.: +49 69 8299381-8
> PGP : http://www.e-trolley.de
>