Replacing

(LEAGUE_USER.OID NOT IN
('SELECT
te.player_oid
from tourney_entry te
where
te.EVENT_OID = ''B423D283-5856-4CBC-9FF4-5D4BC413C061'''))

by

(Not exists LEAGUE_USER.OID NOT IN
('SELECT
1
from tourney_entry te
where
te.player.oid = LEAGUE_USER.OID
and
te.EVENT_OID = ''B423D283-5856-4CBC-9FF4-5D4BC413C061'''))

usually runs a lot faster in FB2.x

----- Original Message -----
From: "Lee Jenkins" <lee@...>
To: "firebird-support" <firebird-support@yahoogroups.com>
Sent: Thursday, July 01, 2010 11:31 AM
Subject: [firebird-support] SQL "NOT IN"


>
> Hi all,
>
> I've done queries like the one below many times and have never had a
> problem
> until now.
>
> SELECT oid,
> region_oid,
> active_date,
> address1,
> address2,
> city,
> province,
> postal_code,
> user_email,
> user_first,
> user_last,
> is_active,
> display_name
> FROM
> league_user
> WHERE
> (LEAGUE_USER.OID NOT IN
> ('SELECT
> te.player_oid
> from tourney_entry te
> where
> te.EVENT_OID = ''B423D283-5856-4CBC-9FF4-5D4BC413C061'''))
> AND
> LEAGUE_USER.FIRST_UPPER LIKE 'LEE%'
> ORDER BY user_first, user_last
>
>
> The problem is that the main query returns records that ARE in the sub
> query.
> If I run the sub query by itself, I can verify that it is returning the
> records
> as it should, but it appears that the main query is not filtering out its
> records according to the results of the sub query.
>
> Any idea? I'm sure its something simple, but I have yet to catch it.
>
> Thanks,
>
> --
> Warm Regards,
>
> Lee
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>