2008/5/1 Ottavio Campana <ottavio@xxxxxxxxxxxxx>: > 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
