Re: Subquery - Mailing list pgsql-novice

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