Re: Subquery - Mailing list pgsql-novice

Awesome, left join + where condition. I guess after 10 years of doing
dbf based work my brain still wants to come up with queries constructed
vertically (table commands on top of table commands) as opposed to
coming up with the more powerful, if I may, horizontal queries.

Thanks a lot Keith.

george

On Tue, 2005-06-21 at 15:34, Keith Worthington wrote:
> George McQuade wrote:
> > Hello List,
> >
> > I have 2 identical tables, table1 and table2 with 2 fields:
> > id int and idname varchar(30). I am successful in retrieving the records
> > in table1 not in table2 with:
> >
> > select id from table1 except select id from table2;
> > id
> > -----
> > 1
> > 2
> > 3
> > ...
> >
> > which is great. It would be even greater if I can get the table1.idname
> > as part of the output, for example:
> >
> > id    idname
> > ----- ------
> > 1     rice
> > 2     beans
> > 3     soy
> > ...
> >
> > something tells me I need to make my query a subquery of something else,
> > but can't quite figure it out.
> >
> > thanks for any pointers
> >
> > george
>
> George,
>
> All you need is a LEFT JOIN and a WHERE IS NULL clause.
>
> This script worked for me.
>
> -- Build table 1.
> CREATE TABLE test_schema.table_1
> (
>    id int2 NOT NULL,
>    id_name varchar(8) NOT NULL
> )
> WITHOUT OIDS;
> ALTER TABLE test_schema.table_1 OWNER TO postgres;
>
> -- Build table 2.
> CREATE TABLE test_schema.table_2
> (
>    id int2 NOT NULL,
>    id_name varchar(8) NOT NULL
> )
> WITHOUT OIDS;
> ALTER TABLE test_schema.table_1 OWNER TO postgres;
>
> -- Populate table 1.
> INSERT INTO test_schema.table_1
>              (
>                  id,
>                  id_name
>              )
>       VALUES ( 1,
>                'tom'
>              );
> INSERT INTO test_schema.table_1
>              (
>                  id,
>                  id_name
>              )
>       VALUES ( 2,
>                'dick'
>              );
> INSERT INTO test_schema.table_1
>              (
>                  id,
>                  id_name
>              )
>       VALUES ( 3,
>                'harry'
>              );
> INSERT INTO test_schema.table_1
>              (
>                  id,
>                  id_name
>              )
>       VALUES ( 4,
>                'jane'
>              );
> INSERT INTO test_schema.table_1
>              (
>                  id,
>                  id_name
>              )
>       VALUES ( 5,
>                'sally'
>              );
> INSERT INTO test_schema.table_1
>              (
>                  id,
>                  id_name
>              )
>       VALUES ( 6,
>                'sue'
>              );
>
> -- Populate table 2.
> INSERT INTO test_schema.table_2
>              (
>                  id,
>                  id_name
>              )
>       VALUES ( 2,
>                'dick'
>              );
> INSERT INTO test_schema.table_2
>              (
>                  id,
>                  id_name
>              )
>       VALUES ( 4,
>                'jane'
>              );
> INSERT INTO test_schema.table_2
>              (
>                  id,
>                  id_name
>              )
>       VALUES ( 6,
>                'sue'
>              );
>
> SELECT * FROM test_schema.table_1;
>   id | id_name
> ----+---------
>    1 | tom
>    2 | dick
>    3 | harry
>    4 | jane
>    5 | sally
>    6 | sue
> (6 rows)
>
> SELECT * FROM test_schema.table_2;
>   id | id_name
> ----+---------
>    2 | dick
>    4 | jane
>    6 | sue
> (3 rows)
>
> -- Retrieve rows in table 1 not in table 2.
> SELECT table_1.id,
>         table_1.id_name
>    FROM test_schema.table_1
>    LEFT JOIN test_schema.table_2
>      ON ( table_1.id = table_2.id )
>   WHERE table_2.id IS NULL;
>
>   id | id_name
> ----+---------
>    1 | tom
>    3 | harry
>    5 | sally
> (3 rows)
>
> --
> Kind Regards,
> Keith