Re: question about join

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