By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Help with Select, Watch out this is a hard one.

P: n/a
Table Structure.

tblData
----------
fldID (AutoNumber) (PK)
fldType (String)
fldLocationID (Number)

tblLocation
---------------
fldID (AutoNumber) (PK)
fldDescription (String)

FKConstraint (tblData.fldLocationID = tblLocation.fldID)

So tblData.fldType is a String that holds the Types of Data, EX =
"OldData", "NewData", "OtherData".
This is used to organize the data by type (Not smart, but I inherited
project can't change now)

So The results I am looking for is to get all "OldData" from any
location where the only type of data for that Location is "OldData".

Here is a select that works, BUT IT IS WAY TO SLOW, since there are
over 30000 records. and over 9000 locations.

SELECT *
FROM tblData
WHERE tblData.fldType = "OldData"
and fldLocationID NOT IN
(SELECT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")

Here is the sample data I have used.

tblLocation
----------------
fldID fldDescription
0 Home
1 Work
2 Unknown

tblData
----------
fldID fldType fldLocationID
10 OldData 0
20 OldData 1
30 NewData 0
40 NewData 2
50 OtherData 0
60 OtherData 2

Proper Results
---------------------
20,OldData,1

Anyone with any ideas would be grateful.

Jordon

Jan 8 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a

jo*********@gmail.com wrote:
Table Structure.

tblData
----------
fldID (AutoNumber) (PK)
fldType (String)
fldLocationID (Number)

tblLocation
---------------
fldID (AutoNumber) (PK)
fldDescription (String)

FKConstraint (tblData.fldLocationID = tblLocation.fldID)

So tblData.fldType is a String that holds the Types of Data, EX =
"OldData", "NewData", "OtherData".
This is used to organize the data by type (Not smart, but I inherited
project can't change now)

So The results I am looking for is to get all "OldData" from any
location where the only type of data for that Location is "OldData".

Here is a select that works, BUT IT IS WAY TO SLOW, since there are
over 30000 records. and over 9000 locations.

SELECT *
FROM tblData
WHERE tblData.fldType = "OldData"
and fldLocationID NOT IN
(SELECT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")
Your approach is the right one, although adding a DISTINCT clause to
the subquery, i.e.,

SELECT *
FROM tblData
WHERE tblData.fldType = "OldData"
and fldLocationID NOT IN
(SELECT DISTINCT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")

might be slightly more efficient. Do you have indexes defined for the
[fldLoactionID] and [fldType] columns?

Here is the sample data I have used.

tblLocation
----------------
fldID fldDescription
0 Home
1 Work
2 Unknown

tblData
----------
fldID fldType fldLocationID
10 OldData 0
20 OldData 1
30 NewData 0
40 NewData 2
50 OtherData 0
60 OtherData 2

Proper Results
---------------------
20,OldData,1

Anyone with any ideas would be grateful.

Jordon
Jan 8 '07 #2

P: n/a
It occurred to me after posting that having

tblData.fldType = "OldData"

in the WHERE clause is redundant given that the subquery already
filters out location IDs that have any other value. So, the following
should work:

SELECT *
FROM tblData
WHERE fldLocationID NOT IN
(SELECT DISTINCT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")

You still should have indexes on the [fldLocationID] and [fldType]
columns.
Gord wrote:
jo*********@gmail.com wrote:
Table Structure.

tblData
----------
fldID (AutoNumber) (PK)
fldType (String)
fldLocationID (Number)

tblLocation
---------------
fldID (AutoNumber) (PK)
fldDescription (String)

FKConstraint (tblData.fldLocationID = tblLocation.fldID)

So tblData.fldType is a String that holds the Types of Data, EX =
"OldData", "NewData", "OtherData".
This is used to organize the data by type (Not smart, but I inherited
project can't change now)

So The results I am looking for is to get all "OldData" from any
location where the only type of data for that Location is "OldData".

Here is a select that works, BUT IT IS WAY TO SLOW, since there are
over 30000 records. and over 9000 locations.

SELECT *
FROM tblData
WHERE tblData.fldType = "OldData"
and fldLocationID NOT IN
(SELECT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")

Your approach is the right one, although adding a DISTINCT clause to
the subquery, i.e.,

SELECT *
FROM tblData
WHERE tblData.fldType = "OldData"
and fldLocationID NOT IN
(SELECT DISTINCT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")

might be slightly more efficient. Do you have indexes defined for the
[fldLoactionID] and [fldType] columns?

Here is the sample data I have used.

tblLocation
----------------
fldID fldDescription
0 Home
1 Work
2 Unknown

tblData
----------
fldID fldType fldLocationID
10 OldData 0
20 OldData 1
30 NewData 0
40 NewData 2
50 OtherData 0
60 OtherData 2

Proper Results
---------------------
20,OldData,1

Anyone with any ideas would be grateful.

Jordon
Jan 9 '07 #3

P: n/a
jo*********@gmail.com wrote:

NOT IN almost always means

"The query will finish but NOT IN your life time."

I would try a Left Join.

SELECT td.*
FROM tblData td
LEFT JOIN
[SELECT fldLocationID FROM tblData WHERE fldType<>"OldData"]. sq
ON td.fldLocationID=sq.fldLocationID
WHERE td.fldType="OldData"
AND sq.fldLocationID IS NULL;

Jan 9 '07 #4

P: n/a
To all,

Sorry for the late reply, I was having problems replying.

Thank you for all your replies. The fldType was not indexed. Once
indexing the field the results were returned very fast. I cannot
believe the system was in place for over 3 years without indexing that
field. (silly me for assuming).

I stayed with my first Select even thought the join would work better,
I needed to add the filter as a where clause only and keep the existing
Joins in place. since it is used all over the program :(.

I did use the distinct though thank you, I would hvae never thought of
it. Even though it is selecting the Primary Key field so distinct
shouldn't make a diference, but I am no expert on MS-Access.

Thank you again,
Jordon

On Jan 9, 6:06 am, "Lyle Fairfield" <lylefairfi...@aim.comwrote:
jordonkr...@gmail.com wrote:NOT IN almost always means

"The query will finish but NOT IN your life time."

I would try a Left Join.

SELECT td.*
FROM tblData td
LEFT JOIN
[SELECT fldLocationID FROM tblData WHERE fldType<>"OldData"]. sq
ON td.fldLocationID=sq.fldLocationID
WHERE td.fldType="OldData"
AND sq.fldLocationID IS NULL;
Jan 16 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.