SELECT help (fwd)

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:

Thanks for the help Darren but that didn't work. It did set me on the
right track however and I came up with the following solution:

SELECT individual.first, individual.last, title_value.title
FROM individual_staff_join_unit
LEFT OUTER JOIN title_value
ON individual_satff_join_unit.title_id = title_value.ttle_id,
individual
WHERE individual.individual_id = 5307809
AND individual_staff_join_unit.main_id = individual.main_id;

On Mon, 14 Jan 2002, Darren Ferguson wrote:

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

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

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@saraswati.wcg.mcgill.ca>
To: <pgsql-general@postgresql.org>
Sent: Monday, January 14, 2002 6:17 PM
Subject: [GENERAL] SELECT help (fwd)