From: | Kevin Lohka <klohka(at)aboutfacedata(dot)ab(dot)ca> |
---|---|
To: | pgsql-novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Help with Select Statement |
Date: | 2004-04-07 20:14:24 |
Message-ID: | 29DF0502-88D0-11D8-8E0B-000A95728606@aboutfacedata.ab.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hello everyone, I have query that I could use some help with.
I have three tables contact, people address.
The contact table has a one to many relationship to people and address
The people and address tables have contact_id as a foreign key. In
addition, the address table has a people_id field.
The address.people_id field can have one of two values. 1) the id of a
person related to the same contact or 2) 0 which indicates the address
is the default record.
I'd like to perform a query which selects:
people.first_name, people.last_name, address.city, address.province
I'm having a problem getting my desired values in the address.city and
address.province fields as there are 3 potential options for each
person.
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.
Thanks for any help.
Kevin Lohka
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Hayward | 2004-04-07 21:02:40 | Everyone can see tables? |
Previous Message | Bruno Wolff III | 2004-04-07 19:59:41 | Re: table space keeps growing. |