Re: question about join

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