Re: SELECT help (fwd) - Mailing list pgsql-general

You could use a LEFT OUTER JOIN on the table with the title = 0

This would return NULLS for that field if it did not exist in the table
but would still return rows

And if the NULL was a problem then you could use the COALESCE function
to change the NULL value to whatever you wanted.

Would look something like this

SELECT individual.first,individual.last,title_value.title
FROM individual
     LEFT OUTER JOIN individual_staff_join_unit ON
          individual_staff_join_unit.main_id = individual.main_id,
     title_value
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;

I think i understood but looking back maybe not. If this is not write then
i misunderstood

But i think it should be fine

Darren Ferguson

On Mon, 14 Jan 2002, David A Dickson wrote:

> 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.dickson@mail.mcgill.ca
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>