Re: select from multiple tables

----- Original Message -----

From: Alessandro Gagliardi

To: Lew

Cc: pgsql-novice@postgresql.org

Sent: Monday, June 18, 2012 5:43 PM

Subject: Re: [NOVICE] select from multiple tables

Would not

SELECT textcol, intcol FROM table1

  JOIN table2 ON (table1.textcol = table2.textcol AND table1.intcol = table2.intcol) 

  JOIN table3 ON (table1.textcol = table3.textcol AND table1.intcol = table3.intcol) 

  JOIN table4 ON (table1.textcol = table4.textcol AND table1.intcol = table4.intcol) 

 WHERE table2.textcol IS NULL AND table2.intcol IS NULL

   AND table3.textcol IS NULL AND table3.intcol IS NULL

   AND table4.textcol IS NULL AND table4.intcol IS NULL;

also work? I'm under the impression that anti-joins (like this) are generally more efficient than nested queries (particularly those with union) though perhaps that depends on indices.

-Alessandro

On Mon, Jun 18, 2012 at 6:43 AM, Lew <noone@lewscanon.com> wrote:

Steve Crawford wrote:

e-letter wrote:

Readers,

A database has table1, table2, table3, table4 and each table has
columns of text and integers:

table1
text1, 10
text2, 20
text3, 30
text4, 40
text5, 50
text6, 60
text7, 70
text8, 80

table2
text1, 10
text2, 20

table3
text3, 30
text4, 40

table4
text5, 50

Is it possible to create a new table (table5) by creating a query that
selects rows that _both_ exist in table1 _and_ are not in any of the
preceding tables (table2, table3, table4)?

Thanks in advance.

create table table5 as
select textcol, intcol from table1 except
(
select textcol, intcol from table2 union
select textcol, intcol from table3 union
select textcol, intcol from table3
);

But why into a separate table?

Isn't just having a SELECT result sufficient? Storing it is a denorm.

Wouldn't a VIEW be better?

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice