Re: SELECT help (fwd)

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(dot)dickson(at)mail(dot)mcgill(dot)ca