Thread: sql

sql

From
"cristi"
Date:
I have 3 tables.
 
table_a
marca
15145
1455
1333
 
table_b
data
11-01-2002
11-02-2002
 
table_c
marca    data
15145    11-01-2002
15145    11-02-2002
1455      11-01-2002
1333      11-01-2002
1333      11-02-2002
 
After interogation of the tables I need this kind of result:
 
marca    data
1455     11-02-2002
 
(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)
 
How can I do that?
 
Thanks!

Re: sql

From
Richard Huxton
Date:
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
---123
(3 rows)

richardh=> SELECT * FROM table_b;    b
------------2002-01-012002-02-022002-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-032 | 2002-01-012 | 2002-03-033 | 2002-01-013 | 2002-02-023 | 2002-03-03
(6 rows)

--  Richard Huxton