From: | Keith Worthington <KeithW(at)NarrowPathInc(dot)com> |
---|---|
To: | George McQuade <gm(at)winls(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Subquery |
Date: | 2005-06-21 20:34:33 |
Message-ID: | 42B879D9.9070108@NarrowPathInc.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | George McQuade | 2005-06-21 22:13:06 | Re: Subquery |
Previous Message | Sigurður Reynisson | 2005-06-21 20:04:07 | Newbie Q:"RETURN cannot have a parameter in function returning set"? |