Re: Help with Select Statement

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