Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: "cristi" <cristi@dmhi.ct.ro>, <pgsql-sql@postgresql.org>
Subject: Re: sql
Date: Mon, 9 Dec 2002 12:04:59 +0000
User-Agent: KMail/1.4.3
References: <008501c29f6f$74aee380$7201a8c0@aaa>
In-Reply-To: <008501c29f6f$74aee380$7201a8c0@aaa>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200212091204.59431.dev@archonet.com>

On Monday 09 Dec 2002 10:40 am, cristi wrote:
> (I want to receive the records which are a combitation of fields of table=
_a
> and table_b and that are not in the table_c)

The following is one way. Not necessarily the most efficient, but it should=
 be=20
clear enough. Basically it builds the product of table_a,table_b then uses =
a=20
LEFT JOIN with WHERE to find items that don't match.

You'll want to test it against your real data to see if it's fast enough.

richardh=3D> SELECT * FROM table_a;
 a
---
 1
 2
 3
(3 rows)

richardh=3D> SELECT * FROM table_b;
     b
------------
 2002-01-01
 2002-02-02
 2002-03-03
(3 rows)

richardh=3D> SELECT * FROM table_c;
 ca |     cb
----+------------
  1 | 2002-01-01
  1 | 2002-02-02
  2 | 2002-02-02
(3 rows)

richardh=3D> \d view_ab
        View "view_ab"
 Column |  Type   | Modifiers
--------+---------+-----------
 a      | integer |
 b      | date    |
View definition: SELECT table_a.a, table_b.b FROM table_a, table_b;

richardh=3D> SELECT a,b FROM view_ab LEFT JOIN table_c ON a=3Dca AND b=3Dcb=
 WHERE ca=20
IS NULL or cb IS NULL ORDER BY a,b;
 a |     b
---+------------
 1 | 2002-03-03
 2 | 2002-01-01
 2 | 2002-03-03
 3 | 2002-01-01
 3 | 2002-02-02
 3 | 2002-03-03
(6 rows)

--=20
  Richard Huxton