Re: Subquery

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