question about join
От | Ottavio Campana |
---|---|
Тема | question about join |
Дата | |
Msg-id | 48197F84.8010208@campana.vi.it обсуждение исходный текст |
Ответы |
Re: question about join
|
Список | pgsql-general |
Hi, I'm having a problem trying to write a query using join, and I hope you can give me a hint. suppose you have a three tables like these: create table first_table ( id serial primary key, description1 text); create table second_table ( id serial primary key, description2 text); create table third_table ( id serial primary key, description3 text, id_ref_first_tab integer references first_table(id), id_ref_second_tab integer references second_table(id), default_value boolean); create unique index idx1 on third_table (id_ref_first_tab,id_ref_second_tab); create unique index idx2 on third_table (id_ref_second_tab) where default_value = true; What I'm trying to do is joining the second and the third tables on second_table.id = third_table.id_ref_second_tab to extract all the values in third_table where id_ref_first_tab has a given value or, in case it is not present, to extract only row that has default_values = true; To further explain, the following query selects both the rows from the join where id_ref_first_tab has the desired value and default_value = true, while I want to select the row corresponding to default_value = true only in case no row corresponding to id_ref_first_tab exists. select * from second_table join third_table on second_table.id = third_table.id_ref_second_tab where id_ref_first_tab = 1 or default_value = true; I hope I've been clear enough... Thanks in advance, Ottavio
Вложения
В списке pgsql-general по дате отправления: