limit with subselect

am Fri, dem 25.07.2008, um 12:02:23 +0200 mailte A B folgendes:

Sorry if this is a double posting, I think the previous message was lost.

I have two tables
T (
id int primary key,
a int,
b int
)

T2 (
id int references T,
c int
);

and I wish to get 20 lines from T like this

select id,a,b from T where id not in (select id from T2 where c=5) limit 20;

but that does not seem to work. How can I get what I want? What 20
records are selected is not important. I just need 20.

Works for me:

test=# create table t (id serial primary key, a int);
NOTICE: CREATE TABLE will create implicit sequence "t_id_seq" for serial column "t.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t"
CREATE TABLE
test=*# create table t2 (id int references t, c int);
CREATE TABLE
test=*# insert into t (a) select * from generate_Series(10,50) s;
INSERT 0 41
test=*# insert into t2 values (1,1);
INSERT 0 1
test=*# insert into t2 values (2,2);
INSERT 0 1
test=*# insert into t2 values (3,3);
INSERT 0 1
test=*# insert into t2 values (4,4);
INSERT 0 1
test=*# select * from t where id not in (select id from t2 where c=3) limit 5;
id | a
----+----
1 | 10
2 | 11
4 | 13
5 | 14
6 | 15
(5 rows)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

Yes it obviously does! I must admit that (as usual) I found the error
shortly after sending the post.
The problem was that instead of

select id,a,b from T where id not in (select id from T2 where c=5)

I wrote

select id,a,b from T where id not in (select YYY from T2 where c=5)

where YYY was a non existant column in T2.
I think that I sometimes got an empty set from the subquery, and sometimes not.

2008/7/25 A. Kretschmer <andreas.kretschmer@schollglas.com>:

On Fri, Jul 25, 2008 at 12:02:23PM +0200, A B wrote:

and I wish to get 20 lines from T like this

select id,a,b from T where id not in (select id from T2 where c=5) limit 20;

but that does not seem to work. How can I get what I want? What 20
records are selected is not important. I just need 20.

You've told us what you're expecting, but not what you're actually
getting so most responses (including this) are going to be guesses! More
information = better responses!

My guess is that you're not getting anything back at all because one of
the "id"s in T2 is null. If that is the case, you need to change the
query to look like:

SELECT id,a,b FROM t WHERE id NOT IN (
SELECT id FROM t2 WHERE c=5 AND id IS NOT NULL)
LIMIT 20;

Sam