May 21, 2007 at 10:25 pm
terrible,terrible problem in ms-access query
getting weired results
below is the query iam pasting
SELECT candidatesRegistration.CANDIid as CANDIid,candidatesRegistration.firstname as firstname, candidatesRegistration.lastname as lastname, candidatesRegistration.keywords as Skills,candidatesRegistration.lastlogin as lastlogin, candidatesRegistration.education as Edu,candidatesRegistration.university as univ, candidatesRegistration.certification as certification,candidatesRegistration.totalexp as totalexp ,candidatesRegistration.companyname,candidatesRegistration.designation FROM candidatesRegistration where 1=1 and ((candidatesRegistration.certification) like '*mcse*'or (candidatesRegistration.certification) like '*ccna*') and ((candidatesRegistration.countryid)=1) and ((candidatesRegistration.keywords) like '*c*'or (candidatesRegistration.keywords) like '*C++*') and ((candidatesRegistration.totalexp)>=1) and ((candidatesRegistration.totalexp)<=11) order by lastlogin desc
first is this a proper query
should i use "*" or "%"
the query says either certification must be mcse or ccna and countryid should be 1 and keywords should be either c or c++ and total exp must be between 1 and 11 years
is this a proper query
iam using ms-access
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 22, 2007 at 12:57 am
any body here pls help,
iam stuck with this problem , cannot move forward at all
is it normal with ms-access
i have over 36000 records
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 22, 2007 at 2:03 am
SELECT candidatesRegistration.CANDIid as CANDIid,candidatesRegistration.firstname as firstname, candidatesRegistration.lastname as lastname, candidatesRegistration.keywords as Skills,candidatesRegistration.lastlogin as lastlogin, candidatesRegistration.education as Edu,candidatesRegistration.university as univ, candidatesRegistration.certification as certification,candidatesRegistration.totalexp as totalexp ,candidatesRegistration.companyname,candidatesRegistration.designation FROM candidatesRegistration where 1=1 and ((candidatesRegistration.jobcategoryid)=39) and ((candidatesRegistration.certification) like '*ccna*' ) and ((candidatesRegistration.countryid)=1) and ((candidatesRegistration.keywords) like '*c++*' ) and ((candidatesRegistration.totalexp)>=1) and ((candidatesRegistration.totalexp)<=10) order by lastlogin desc
the same query works in ms-acess query builder but doesnt work when iam firing that query with asp
pls help whats wrong
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 22, 2007 at 4:45 am
Try replacing * with %.
May 22, 2007 at 6:52 am
Are the tables in Access or in SQL? * is the access wildcard. % is the sql equivalent.
p.s. using wildcards like you are will almost guarentee terrible performance with large amounts of records. From what I guess of the table structure based on your query, it seriously needs normalisation.
p.p.s. The cause "candidatesRegistration.keywords) like '*c*" will get you many things you probably didn't expect, including "Visual Basic", "C#", "Access", "Javascript", etc
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply