mytfein
asked on
SQL queston: SELECT /WHERE EXISTS CLAUSE -- ACCESS 2000 platform
Greetings:
I would appreciate help with understanding:
SELECT/WHERE EXISTS
1) Do you agree with the following URL:
http://www.bcarter.com/sap18.htm
that it's more efficient to use WHERE EXISTS then coding:
SELECT COUNT(fieldA)
then tesing If COUNT(fieldA) > 0
2) Do you agree with the following URL that the results of a SELECT/WHERE EXISTS returns
a boolean TRUE/FALSE?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_exists.asp
3) I want to code a SELECT/WHERE EXISTS via VBA for ACCESS.
In my situation, I just want to see if a value simply exists only once
( I am aware that I could use the Access function: DLOOKUP,
I just want to accomplish via SQL)
Background:
The table has a field to hold a physical box number.
When employees are archived, they are placed in a box and sent to offsite-storage.
The box number id# is mm/dd/yy-#### such as 07/01/04-0001
07/01/04-0002
When employee is archived, it's written to a log table, so I do not have a box# master table.
So if 10 employees were archived to box 07/01/04-0001, then 10 rows would exist in the log table.
I'm just interested if 07/01/04 exists as a box# at all.
I was hoping that the SELECT/WHERE EXISTS would return a boolean value......
I create an ACCESS query object, and have gotten it to work, but:
a) without returning an IMPLICT 1 row result set of just a boolean value
b) since I have 10 rows with 07/01/04-0001, am getting 10 rows of results, so added the
DISTINCT to the SELECT/WHERE EXISTS
SELECT DISTINCT True ' I am explicitly defining a BOOLEAN VALUE
FROM ZT_000_STAFF_INFO_LOG AS T1
WHERE Exists (select Left(T2.InfoArchiveBox, 8)
FROM ZT_000_STAFF_INFO_LOG as T2
WHERE Left(T2.InfoArchiveBox, 8) = "07/01/04"
and Left(T1.InfoArchiveBox, 8) = Left (T2.InfoArchiveBox, 8))
Am I on the right track? Can I get the SQL to return a boolean value? Is the above more/less efficient than testing count(fielda)?
Your feedback is most appreciated! thank you!
I would appreciate help with understanding:
SELECT/WHERE EXISTS
1) Do you agree with the following URL:
http://www.bcarter.com/sap18.htm
that it's more efficient to use WHERE EXISTS then coding:
SELECT COUNT(fieldA)
then tesing If COUNT(fieldA) > 0
2) Do you agree with the following URL that the results of a SELECT/WHERE EXISTS returns
a boolean TRUE/FALSE?
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlce/htm/_lce_exists.asp
3) I want to code a SELECT/WHERE EXISTS via VBA for ACCESS.
In my situation, I just want to see if a value simply exists only once
( I am aware that I could use the Access function: DLOOKUP,
I just want to accomplish via SQL)
Background:
The table has a field to hold a physical box number.
When employees are archived, they are placed in a box and sent to offsite-storage.
The box number id# is mm/dd/yy-#### such as 07/01/04-0001
07/01/04-0002
When employee is archived, it's written to a log table, so I do not have a box# master table.
So if 10 employees were archived to box 07/01/04-0001, then 10 rows would exist in the log table.
I'm just interested if 07/01/04 exists as a box# at all.
I was hoping that the SELECT/WHERE EXISTS would return a boolean value......
I create an ACCESS query object, and have gotten it to work, but:
a) without returning an IMPLICT 1 row result set of just a boolean value
b) since I have 10 rows with 07/01/04-0001, am getting 10 rows of results, so added the
DISTINCT to the SELECT/WHERE EXISTS
SELECT DISTINCT True ' I am explicitly defining a BOOLEAN VALUE
FROM ZT_000_STAFF_INFO_LOG AS T1
WHERE Exists (select Left(T2.InfoArchiveBox, 8)
FROM ZT_000_STAFF_INFO_LOG as T2
WHERE Left(T2.InfoArchiveBox, 8) = "07/01/04"
and Left(T1.InfoArchiveBox, 8) = Left (T2.InfoArchiveBox, 8))
Am I on the right track? Can I get the SQL to return a boolean value? Is the above more/less efficient than testing count(fielda)?
Your feedback is most appreciated! thank you!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
3a) I figured it out because the where clausule in subselect:
WHERE Left(T2.InfoArchiveBox, 8) = "07/01/04"
and Left(T1.InfoArchiveBox, 8) = Left (T2.InfoArchiveBox, 8)
Logically that's mean that Left(T1.InfoArchiveBox, 8) = "07/01/04", right? ;-)
So you won't need the subselect.
b) That depends in what are you checking.
2) EXISTS is a SQL Server function, but you can make your own Access Exists function, that receive a SQL Statement, then open a recorset and see if recordset.recordcount > 0 return true, else return false.
I hope that can help you.
WHERE Left(T2.InfoArchiveBox, 8) = "07/01/04"
and Left(T1.InfoArchiveBox, 8) = Left (T2.InfoArchiveBox, 8)
Logically that's mean that Left(T1.InfoArchiveBox, 8) = "07/01/04", right? ;-)
So you won't need the subselect.
b) That depends in what are you checking.
2) EXISTS is a SQL Server function, but you can make your own Access Exists function, that receive a SQL Statement, then open a recorset and see if recordset.recordcount > 0 return true, else return false.
I hope that can help you.
ASKER
Hi VMontalvao,
Thanks for your assistance, posted the points for your help.....
Thanks for your assistance, posted the points for your help.....
ASKER
Thx for your quick response....
Reading your responses backwards:
3) I did not realize that a "fancy" SELECT/where EXISTS is not needed for my goal.
thx for pointing it out....
a) Until I get more experience, how did you figure it out,
Is it because the inner SELECT and the outer SELECT both used the same table name?
b) To double-check: Is (3) a better approach than (1) to check if a value is on a table?
2) Suppose I had a meaningful SELECT/where exists statement,
for the results to be boolean TRUE or FALSE,
does it need to be clearly coded like this:
SELECT DISTINCT True
because I was told that Access 2000 vba/sql does not support "IF EXISTS",
and Access 2000 vba/sql supports SELECT/WHERE EXISTS
or are you aware of another approach,
if you possibly has a SELECT/WHERE EXISTS code snippet that was written for access 2000, where the results is a boolean true or false, without having to code it explicitly in the SELECT.....I would love to see it.....
thx in advance, mytfein