Re: SELECT help (fwd)
От | David A Dickson |
---|---|
Тема | Re: SELECT help (fwd) |
Дата | |
Msg-id | Pine.LNX.4.33.0201141612550.26621-100000@blues.wcg.mcgill.ca обсуждение исходный текст |
Ответ на | Re: SELECT help (fwd) (Darren Ferguson <darren@crystalballinc.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: