AW: V4MD Looking for ideas and solutions for searching doubleentries

[Ruslan Zasukhin]

on 10/2/03 7:01 PM, Florian Bogeschdorfer at fb at memedia.de wrote:

>>        SOLUTION
>>        --------
>>        
>> 
>> select recID, name, forename,
>> ( select count(*) from test1 t1 where
>>                         t1.name = t.name
>>                 and     t1.forename = t.forename ) as c
>>  from test1 t
>> order by c DESC, name, forename
>> 
>> 
>> Result is
>> 1       Myers   Mike    2
>> 5       Myers   Mike    2
>> 2       Stone   Tom     2
>> 4       Stone   Tom     2
>> 3       Shields Brooke  1
>> 6       Willis  Bruce   1
>> 
>> 
>> Now you have a number of opportunities
>> You can analyze records in the cursor till c != 1 for example
>> 
> I tried to translate this (with and without AS)
> 
> SELECT recID, name, vorname, ( SELECT COUNT(*) FROM KUNDENtable t1 WHERE
> t1.name = t.name and t1.vorname = t.vorname ) AS c FROM KUNDENtable t ORDER
> BY c DESC, name, vorname"
> 
> But get error "field not found". It must be 'c' maybe?
> 
> Ruslans solution:
> 
>   select a.RecID, a.name, a.lname
>   FROM table a, table b
>   WHERE 
>           a.name = b.name
>       and a.lname = b.lname
>   ...
>   
> 
> I have 50 records in the DB and this returns a cursor with 68 records?

Florian, please note, Ivan describe solution for Oracle, not for Valentina
!!!

In Valentina 1.x above SQL not works.


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://lists.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------