Re: where not unique

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.