troubleshooting Question

SQL queston: SELECT /WHERE EXISTS CLAUSE  -- ACCESS 2000 platform

Avatar of mytfein
mytfein asked on
Microsoft SQL Server
4 Comments1 Solution301 ViewsLast Modified:
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!
                           
ASKER CERTIFIED SOLUTION
Vitor Montalvão
IT Engineer
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros