Skip site navigation (1) Skip section navigation (2)

Help with Select Statement

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 (view raw or flat)
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


Responses

pgsql-novice by date

Next:From: Richard HaywardDate: 2004-04-07 21:02:40
Subject: Everyone can see tables?
Previous:From: Bruno Wolff IIIDate: 2004-04-07 19:59:41
Subject: Re: table space keeps growing.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group