Re: [SQL] where not unique

[Daniel Henrique Alves Lima]

Gary Stainburn wrote:

Hi folks,

I've got a table which contains amongst other things a stock number and a 
registration number.  I need to print out a list of stock number and reg 
number where reg number is not unique (cherished number plate transfer not 
completed).  I've tried variations of a theme based on 

select stock_number, registration from stock where registration in 
 (select registration, count(registration) as count 
    from stock where count > 1 group by registration);

but I have two problems.  Firstly with the sub-select I get:

usedcars=# select registration, count(registration) as count from stock where 
count > 1 group by registration;
ERROR:  Attribute 'count' not found
usedcars=#

although if I miss out the where clause I get the expected results.

   I think that this is not the best way, but :

select * from (select registration, count(registration) as counter from 
stock group by registration)
where counter > 1;

Secondly, when I run the full query I get:

usedcars=# select stock_number, registration from stock
usedcars-# where registration in
usedcars-# (select registration, count(registration) as count from stock group 
by registration);
ERROR:  Subselect has too many fields
usedcars=#
 

   This is because the subselect is returning 2 columns but "in clause" 
is expecting just one column.
Try to use "exists" instead of "in", ok ?

which is obviously because of the count field. 

Can anyone tell me where I'm going wroing with these count fields?
(I've tried renaming the field to regcount in case it was a reserved word 
problem).
 

   I hope this helps you.



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly