From: Richard Huxton <dev@archonet.com>
To: cristi <cristi@dmhi.ct.ro>
To: pgsql-sql@postgresql.org
Subject: Re: sql
Date: Mon, 9 Dec 2002 12:04:59 +0000
Message-ID: <200212091204.59431.dev@archonet.com> (raw)
In-Reply-To: <008501c29f6f$74aee380$7201a8c0@aaa>
References: <008501c29f6f$74aee380$7201a8c0@aaa>
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
clear enough. Basically it builds the product of table_a,table_b then uses a
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=> SELECT * FROM table_a;
a
---
1
2
3
(3 rows)
richardh=> SELECT * FROM table_b;
b
------------
2002-01-01
2002-02-02
2002-03-03
(3 rows)
richardh=> SELECT * FROM table_c;
ca | cb
----+------------
1 | 2002-01-01
1 | 2002-02-02
2 | 2002-02-02
(3 rows)
richardh=> \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=> SELECT a,b FROM view_ab LEFT JOIN table_c ON a=ca AND b=cb WHERE ca
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)
--
Richard Huxton
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, cristi@dmhi.ct.ro
Subject: Re: sql
In-Reply-To: <200212091204.59431.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox