question about join

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