Re: stupid SQL question, how reach different rows of two almost same tables

On Thu, Oct 06, 2005 at 12:53:14PM +0200,  Jaromír Kamler wrote:
> ERROR:  subquery in FROM must have an alias
> HINT:  For example, FROM (SELECT ...) [AS] foo.
>
> In statement:
> create temp table t1 as
> select * from (select f_table_name from geometry_columns intersect select jmeno from tables) as tmp_foo;
>
> select * from (select f_table_name from geometry_columns except select jmeno from tables)) as tmp_foo1
> union
> select * from (select jmeno from tables except select f_table_name from geomety_columns) as tmp_foo2;

The first query in the union has an extra right parenthesis; the
second query has a misspelled table name ("geomety_columns" instead
of "geometry_columns").

> I was traying somethin like this, but it looks like wrong way:
> SELECT f_table_name FROM geometry_columns WHERE (SELECT jmeno FROM tables) NOT IN (SELECT f_table_name FROM
geometry_columns);

The name geometry_columns suggests that you're using PostGIS.  What
are you trying to do?  Are you looking for values of tables.jmeno
that aren't in geometry_columns.f_table_name, or vice versa, or
both?  Maybe this example will help:

CREATE TABLE geometry_columns (f_table_name text);
CREATE TABLE tables (jmeno text);

INSERT INTO geometry_columns (f_table_name) VALUES ('only in geometry_columns');
INSERT INTO geometry_columns (f_table_name) VALUES ('in both');

INSERT INTO tables (jmeno) VALUES ('only in tables');
INSERT INTO tables (jmeno) VALUES ('in both');

SELECT f_table_name FROM geometry_columns EXCEPT SELECT jmeno FROM tables;
       f_table_name
--------------------------
 only in geometry_columns
(1 row)

SELECT jmeno FROM tables EXCEPT SELECT f_table_name FROM geometry_columns;
     jmeno
----------------
 only in tables
(1 row)

(SELECT f_table_name FROM geometry_columns EXCEPT SELECT jmeno FROM tables)
UNION
(SELECT jmeno FROM tables EXCEPT SELECT f_table_name FROM geometry_columns);
       f_table_name
--------------------------
 only in geometry_columns
 only in tables
(2 rows)

SELECT f_table_name FROM geometry_columns INTERSECT SELECT jmeno FROM tables;
 f_table_name
--------------
 in both
(1 row)

--
Michael Fuhr