Re: question about join

2008/5/1 Ottavio Campana <ottavio(at)campana(dot)vi(dot)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