Обсуждение: 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