question about join

From: Ottavio Campana <ottavio(at)campana(dot)vi(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: question about join
Date: 2008-05-01 08:29:56
Message-ID: 48197F84.8010208@campana.vi.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Anton Burkun 2008-05-01 08:56:39 Compiling trigger function with MinGW
Previous Message Greg Smith 2008-05-01 05:30:01 Re: How to modify ENUM datatypes?