Optimizing a SQL query 1

[w11z]

Hello everyone,

I am calling on you to see how I can optimize these SQL queries (access and ASP).

Base Query :

tSql = "SELECT sectionID, detailSectionID, banID, judgeID, raceID, sectionURL, nbrIns, nbrExp FROM tblDetailSections WHERE detailExpoID = 111
Set Res = dbLink.Execute(tSql)


Other queries related to the Base query :

tSql = "SELECT sectionID, nomSection FROM tblSections WHERE sectionID = " & Res("sectionID")
Set ResNom = dbLink.Execute(tSql)

tSql = "SELECT judgeID, nameJudge, city FROM tblJudges WHERE judgeID = " & Res("judgeID")
Set ResJuge = dbLink.Execute(tSql)

tSql = "SELECT banID, banUrl, banPath FROM tblBans WHERE banID = " & Res("banID")
Set resBan = dbLink.Execute(tSql)

tSql = "SELECT raceID, descRace FROM tblRaces WHERE raceID = " & Res("raceID")
Set resRace = dbLink.Execute(tSql)


How can I put everything together instead of having 5 different queries?

Thank you

w11z

 

[PHV]

Something like this ?
tSql = "SELECT S.sectionID,D.nomSection,S.detailSectionID,S.banID,B.banUrl,B.banPath" _
& ",S.judgeID,J.nameJudge,J.city,S.raceID,R.descRace,S.sectionURL,S.nbrIns,S.nbrExp" _
& " FROM (((tblDetailSections D" _
& " LEFT JOIN tblSections S ON D.sectionID=S.sectionID)" _
& " LEFT JOIN tblJudges J ON D.judgeID=J.judgeID)" _
& " LEFT JOIN tblBans B ON D.banID=B.banID)" _
& " LEFT JOIN tblRaces R ON D.raceID=R.raceID" _
& " WHERE detailExpoID = 111"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[w11z]

Thanks PHV Works great!