Обсуждение: Help with Select Statement

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

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

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
>

Kevin Lohka wrote:
> 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.

There are several ways to return only 1 result out of many:
GROUP BY ( for aggregate functions like max, min, avg )
DISTINCT ( quick & dirty ;)
LIMIT    ( probably together with ORDER BY cut the result )

And remember: whenever you run into a hopeless situation: you can still use
subqueries in postgresql :)

--
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de

Hi,

I am accessing my db through ODBC driver of postgresql. What will be the
syntax to call a PL/pgSQL function
through ODBC using C++? I am using CDatabase & CRecordset of VisualC++.


Thanks,