Select query and use of IN

[Bart Pietercil]

Thx Ruslan,

Had already made the workaround in RB

aQuery = " "SELECT recid, competence_name FROM competence_cards WHERE  
org_pt = 1"

if list.Listcount > 0 Then
aQuery = aQuery + "AND recid NOT IN (SELECT compcard_ptr FROM  
mm_compcard_functprofile WHERE profile_ptr = 1)"
end if

runquery(aquery)

Just for archives :-)

Bart Pietercil

On 1-mrt-07, at 19:04, Ruslan Zasukhin wrote:

> On 1/3/07 6:56 PM, "Bart Pietercil" <bart.pietercil at gmail.com> wrote:
>
> Hi Bart,
>
>>    "SELECT recid, competence_name FROM competence_cards WHERE org_ptr
>> = 1 AND recid NOT IN (SELECT compcard_ptr FROM
>> mm_compcard_functprofile WHERE profile_ptr = 1)"
>
> We need test
>
>> This query is not returning the expected result (expected = all
>> records, returned = empty) when the subselect is empty but works when
>> the subselect has recid's?
>>
>> How would I need to rewrite I so that all recid from competence_cards
>> (with org_ptr= 1) are returned when there are no compcard_ptr with
>> profile_ptr = 1 in mm_compcard_functprofile
>
> Try as fast workaround:
>
>>    "SELECT recid, competence_name FROM competence_cards WHERE org_ptr
>> = 1 AND recid IN (SELECT compcard_ptr FROM
>> mm_compcard_functprofile WHERE profile_ptr <> 1)"
>
>
> -- 
> Best regards,
>
> Ruslan Zasukhin
> VP Engineering and New Technology
> Paradigma Software, Inc
>
> Valentina - Joining Worlds of Information
> http://www.paradigmasoft.com
>
> [I feel the need: the need for speed]
>
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina