Re: SELECT help (fwd)
От | Darren Ferguson |
---|---|
Тема | Re: SELECT help (fwd) |
Дата | |
Msg-id | Pine.LNX.4.10.10201141545490.10749-100000@thread.crystalballinc.com обсуждение исходный текст |
Ответ на | SELECT help (fwd) (David A Dickson <davidd@saraswati.wcg.mcgill.ca>) |
Ответы |
Re: SELECT help (fwd)
(David A Dickson <davidd@saraswati.wcg.mcgill.ca>)
|
Список | 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 >
В списке pgsql-general по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: How to send automatically a NOTIFY when a COMMIT is done ?