question about join

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enig12FE4DDE710DF65CF767E594
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: quoted-printable

Hi, I'm having a problem trying to write a query using join, and I hope=20
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=20
(id_ref_first_tab,id_ref_second_tab);

create unique index idx2 on third_table (id_ref_second_tab) where=20
default_value =3D true;

What I'm trying to do is joining the second and the third tables on=20
second_table.id =3D third_table.id_ref_second_tab to extract all the=20
values in third_table where id_ref_first_tab has a given value or, in=20
case it is not present, to extract only row that has default_values =3D t=
rue;

To further explain, the following query selects both the rows from the=20
join where id_ref_first_tab has the desired value and default_value =3D=20
true, while I want to select the row corresponding to default_value =3D=20
true only in case no row corresponding to id_ref_first_tab exists.

select * from second_table join third_table on second_table.id =3D=20
third_table.id_ref_second_tab where id_ref_first_tab =3D 1 or=20
default_value =3D true;

I hope I've been clear enough...

Thanks in advance,

Ottavio


--------------enig12FE4DDE710DF65CF767E594
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (Darwin)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFIGX+MsbKx4ZV8clIRAnvlAJ9Pnt8OHntL8Pl38IIQT0iu62m4hQCbBNZn
ne6I1dHNlVHX6KqwSPqnl20=
=+LPu
-----END PGP SIGNATURE-----

--------------enig12FE4DDE710DF65CF767E594--