Обсуждение: question about join
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
Вложения
2008/5/1 Ottavio Campana <ottavio@campana.vi.it>: > 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... > Try: select * from second_table join third_table on second_table.id = third_table.id_ref_second_tab where id_ref_first_tab = 1 or (id_ref_first_tab <> 1 and default_value = true); Osvaldo
Osvaldo Kussama ha scritto: >> 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... > > Try: > select * from second_table join third_table on second_table.id = > third_table.id_ref_second_tab > where id_ref_first_tab = 1 or (id_ref_first_tab <> 1 and default_value = true); it's not what I want, because it can return two rows, while I want only one row back, checking the first condition and optionally the second one only if the first one is not matched. I don't know if it is possible, but if it could, it would be great. -- Non c'e' piu' forza nella normalita', c'e' solo monotonia.
Вложения
2008/5/1 Ottavio Campana <ottavio@campana.vi.it>: > Osvaldo Kussama ha scritto: > > > > > > > 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... > > > > > > > Try: > > select * from second_table join third_table on second_table.id = > > third_table.id_ref_second_tab > > where id_ref_first_tab = 1 or (id_ref_first_tab <> 1 and default_value = > true); > > > > it's not what I want, because it can return two rows, while I want only one > row back, checking the first condition and optionally the second one only if > the first one is not matched. > > I don't know if it is possible, but if it could, it would be great. > > -- > Non c'e' piu' forza nella normalita', c'e' solo monotonia. > > SELECT * FROM second_table JOIN third_table ON second_table.id = third_table.id_ref_second_tab WHERE id_ref_first_tab = 1 UNION SELECT * FROM second_table JOIN third_table ON second_table.id = third_table.id_ref_second_tab WHERE default_value = true AND NOT EXISTS(SELECT * FROM second_table JOIN third_table ON second_table.id = third_table.id_ref_second_tab WHERE id_ref_first_tab = 1); Osvaldo