Re: select from multiple tables

On 06/12/2012 11:16 AM, 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
);

Cheers,
Steve