Re: SELECT help (fwd)

David,

I think you will need to use an outer join to get the title=0 records look
at:

http://www.postgresql.org/idocs/index.php?queries.html#QUERIES-FROM

specifically the information relating to Left Outer Join.

hih

steve boyle

----- Original Message -----
From: "David A Dickson" <davidd(at)saraswati(dot)wcg(dot)mcgill(dot)ca>
To: <pgsql-general(at)postgresql(dot)org>
Sent: Monday, January 14, 2002 6:17 PM
Subject: [GENERAL] SELECT help (fwd)

> I am trying to do a select similar to the one below:
>
> SELECT individual.first, individual.last, title_value.title
> FROM individual, title_value, individual_staff_join_unit
> WHERE individual.individual_id = 5307809
> AND
> individual_staff_join_unit.main_id = individual.main_id
> AND
> title_value.title_id = individual_staff_join_unit.title_id;
>
> Table "individual"
> Attribute | Type | Modifier
> ------------------+------------------------+----------
> individual_id | integer |
> main_id | integer |
> first | character varying(40) |
> last | character varying(40) |
>
> Table "individual_staff_join_unit"
> Attribute | Type | Modifier
> -------------+---------+----------
> main_id | integer |
> unit_id | integer |
> title_id | integer |
>
> Table "title_value"
> Attribute | Type | Modifier
> -----------+-----------------------+----------
> title_id | integer |
> title | character varying(40) |
>
> Every individual has a individual_id and a main_id.
>
> The problem is that some rows in the individual_staff_join_unit table have
> title_id = 0 and there is no row in title_value with title = 0. If this is
> the case then no row is retrieved for the above SELECT.
>
> Q: Is it possible to still get the individual.first and individual.last
> from the table if the individual_staff_join_unit.title_id = 0 using only
> one select statement and without modifying any of the tables, and to get
> individual.first, individual.last and title_value.title if
> individual_staff_join_unit.title_id != 0?
>
> --
> David A Dickson
> david(dot)dickson(at)mail(dot)mcgill(dot)ca
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>