Kevin,
Create Cursor curA (Id C(20))
Insert Into curA Values ("A01")
Insert Into curA Values ("B01")
Insert Into curA Values ("C01")
Insert Into curA Values ("D01")
*
Create Cursor curB (Id C(20))
Insert Into curB Values ("Z01")
Insert Into curB Values ("B01")
Insert Into curB Values ("C01")
Insert Into curB Values ("E01")
Select all ;
curA.Id ;
>From curB ;
Right Join curA On curB.Id=curA.Id ;
where Isnull(curB.id)
This gives you records in the left table which aren't in the right table and
should get you started.
Dave Crozier
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Kevin O'Shea
Sent: 23 April 2008 15:26
To: [email protected]
Subject: Looking to optimize a query
VFP 9 SP1...
A colleague is looking for a way to optimize a query... a little background
- we are looking to allow users to define what records they want to see in
our lookup lists.
The approach taken so far is to present the admin user with a list of all
the lookup records. The admin user than removes all the records they don't
want to see. We store these records that were selected NOT to see in a
table. The thinking here is that this will be less fields to store - ones
they don't want to see as opposed to ones they do want to see.
When building the lookup lists, we looked at using a
SELECT FROM maintable WHERE field1+field2+field3 NOT IN (SELECT
field1+field2+field3 FROM filterlisttable)
This SELECT is taking from 3 to 5 seconds (3 seconds with an index, 5
without) on a few thousand records - not ideal.
We looked at JOINS but there doesn't seem to be one that handles only
returning the records in my left table that aren't in my right table.
Any thoughts on an approach to build this SELECT statement to make it
faster? Unfortunately at this time, we are also forced with the
concatenating of these fields - there is no unique field to pull from.
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
