Select query and use of IN

[Ruslan Zasukhin]

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]