byta ut where mot join

hejsan, jag har här en skaplig sqlsträng som jag skulle behöva redigera en del i.

strSQL = "SELECT Ac.Id AS Acid, Ac.Areaid AS Acareaid, " & _
"Ac.Description AS Acdescription, Ac.Userid AS Acuserid, " & _
"Ac.Username AS Acusername, Ac.Notes AS Acnotes, " & _
"Ac.Currentnr AS Accurrentnr, Ac.Startdate AS Acstartdate, " & _
"Ac.Stopdate AS Acstopdate, Us.Id AS UsId, " & _
"Us.Username AS Ususername, Ar.Areaname AS Arareaname, " & _
"Co.Ok AS Cook, Co.Proceed As Coproceed " & _
"FROM Activitytbl Ac, Usertbl Us, Areatbl Ar, Controltbl Co " & _
"WHERE Ac.Userid = Us.Id AND Ac.Areaid = Ar.Id AND Ac.Areaid = " & Request.Querystring("Areaid") & " AND Ac.Id = Co.Id " & _
"ORDER BY " & ordervalue & " ASC;"

koden fungerar som den är, men inte riktigt som jag vill.
det handlar ju om fyra tabeller: activitytbl, areatbl, usertbl och controltbl.
jag skulle vilja ha inner joins mellan:
activitytbl - areatbl
activitytbl - usertbl

samt en left join mellan:
activitytbl - controltbl

är detta möjligt?
om inte, kan man spara min kod fast man byter ut och gör en left join på: activitytbl - controltbl?

mycket tacksam om någon orkar svara.

------------------
Motto: Flest If-taggar vinner!

strSQL = "SELECT Ac.Id AS Acid, Ac.Areaid AS Acareaid, " & _
"Ac.Description AS Acdescription, Ac.Userid AS Acuserid, " & _
"Ac.Username AS Acusername, Ac.Notes AS Acnotes, " & _
"Ac.Currentnr AS Accurrentnr, Ac.Startdate AS Acstartdate, " & _
"Ac.Stopdate AS Acstopdate, Us.Id AS UsId, " & _
"Us.Username AS Ususername, Ar.Areaname AS Arareaname, " & _
"Co.Ok AS Cook, Co.Proceed As Coproceed " & _
"FROM ((Activitytbl Ac inner join  Usertbl Us on Ac.Userid = Us.Id ) "&_
" inner join  Areatbl Ar on Ac.Areaid = Ar.Id) left join  Controltbl Co on  Ac.Id = Co.Id " & _
"ORDER BY " & ordervalue & " ASC"

------------------
essentitia preter non sans multiplicandum

Åhh, jag har precis fått en idol!
LarsG, du är en ängel :f

------------------
Motto: Flest If-taggar vinner!

fortsättning...

strSQL = "SELECT Ac.Id AS Acid, Ac.Areaid AS Acareaid, " & _
"Ac.Description AS Acdescription, Ac.Userid AS Acuserid, " & _
"Ac.Username AS Acusername, Ac.Notes AS Acnotes, " & _
"Ac.Currentnr AS Accurrentnr, Ac.Startdate AS Acstartdate, " & _
"Ac.Stopdate AS Acstopdate, Ac.Place AS Acplace, " & _
"Us.Id AS UsId, Us.Username AS Ususername, " & _
"Ar.Areaname AS Arareaname, Ar.Objectid AS Arobjectid, " & _
"Co.Ok AS Cook, Co.Notes AS Conotes, " & _
"Co.Proceed As Coproceed, Co.Sign AS Cosign " & _
"FROM ((Activitytbl Ac INNER JOIN Usertbl Us ON Ac.Userid = Us.Id) "&_
"INNER JOIN Areatbl Ar ON Ac.Areaid = Ar.Id) LEFT JOIN Controltbl Co ON Ac.Id = Co.Activityid " & _
"WHERE Ac.Areaid = " & Request.Querystring("Areaid") & " ORDER BY " & ordervalue & " ASC"

är det möjligt att bygga på den med en tabell till, skulle behöva ha denna selectsats inbakad:

select Ob.Objectname AS Obobjectname
from Areatbl Ar inner join Objecttbl Ob On Ar.Objectid = Ob.Id

jag vill alltså baka in Objecttbl i selectsatsen och joina den med Areatbl.
:OO det borde väl inte vara något problem, eller?

(LarsG, do your stuff!!!)

------------------
Motto: Flest If-taggar vinner!

strSQL = "SELECT Ac.Id AS Acid, Ac.Areaid AS Acareaid, " & _
"Ac.Description AS Acdescription, Ac.Userid AS Acuserid, " & _
"Ac.Username AS Acusername, Ac.Notes AS Acnotes, " & _
"Ac.Currentnr AS Accurrentnr, Ac.Startdate AS Acstartdate, " & _
"Ac.Stopdate AS Acstopdate, Us.Id AS UsId, " & _
"Us.Username AS Ususername, Ar.Areaname AS Arareaname, " & _
"Co.Ok AS Cook, Co.Proceed As Coproceed,Ob.Objectname AS Obobjectname " & _
"FROM (((Activitytbl Ac inner join Usertbl Us on Ac.Userid = Us.Id ) "&_
" inner join Areatbl Ar on Ac.Areaid = Ar.Id)) " & _
" inner join Objecttbl Ob On Ar.Objectid = Ob.Id) " & _
" left join Controltbl Co on Ac.Id = Co.Id " & _
"ORDER BY " & ordervalue & " ASC"

------------------
essentitia preter non sans multiplicandum

det måste vara något parantesfel på nått vis, detta händer:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
jag testade lite olika varianter med paranteserna men får inte till det!

------------------
Motto: Flest If-taggar vinner!

Jag skrev lite fel. Om du använder SQL server så kan du ta bort parenteserna helt och hållet. Det är bara Access som kräver dom.

strSQL = "SELECT Ac.Id AS Acid, Ac.Areaid AS Acareaid, " & _
"Ac.Description AS Acdescription, Ac.Userid AS Acuserid, " & _
"Ac.Username AS Acusername, Ac.Notes AS Acnotes, " & _
"Ac.Currentnr AS Accurrentnr, Ac.Startdate AS Acstartdate, " & _
"Ac.Stopdate AS Acstopdate, Us.Id AS UsId, " & _
"Us.Username AS Ususername, Ar.Areaname AS Arareaname, " & _
"Co.Ok AS Cook, Co.Proceed As Coproceed,Ob.Objectname AS Obobjectname " & _
"FROM (((Activitytbl Ac inner join Usertbl Us on Ac.Userid = Us.Id ) "&_
" inner join Areatbl Ar on Ac.Areaid = Ar.Id) " & _
" inner join Objecttbl Ob On Ar.Objectid = Ob.Id) " & _
" left join Controltbl Co on Ac.Id = Co.Id " & _
"ORDER BY " & ordervalue & " ASC"

------------------
essentitia preter non sans multiplicandum

nu funkar det!!

strSQL = "SELECT Ac.Id AS Acid, Ac.Areaid AS Acareaid, " & _
"Ac.Description AS Acdescription, Ac.Userid AS Acuserid, " & _
"Ac.Username AS Acusername, Ac.Notes AS Acnotes, " & _
"Ac.Currentnr AS Accurrentnr, Ac.Startdate AS Acstartdate, " & _
"Ac.Stopdate AS Acstopdate, Ac.Place AS Acplace, " & _
"Us.Id AS UsId, Us.Username AS Ususername, " & _
"Ar.Areaname AS Arareaname, Ar.Objectid AS Arobjectid, " & _
"Co.Ok AS Cook, Co.Notes AS Conotes, " & _
"Co.Proceed As Coproceed, Co.Sign AS Cosign, " & _
"Ob.Objectname AS Obobjectname, Pr.Ok AS Prok, Pr.Sign AS Prsign " & _
"FROM Activitytbl Ac inner join Usertbl Us on Ac.Userid = Us.Id "&_
" inner join Areatbl Ar on Ac.Areaid = Ar.Id " & _
" inner join Objecttbl Ob On Ar.Objectid = Ob.Id " & _
" left join Controltbl Co on Ac.Id = Co.Id " & _
" left join Proceedingtbl Pr on Co.Id = Pr.Controllid " & _
"WHERE Ac.Areaid = " & Request.Querystring("Areaid") & " ORDER BY " & ordervalue & " ASC"

:D Tack LarsG!

jag bygde även på den med ännu en tabell, finns det någon fara med att joina så här många tabeller?
någonting annat man bör tanka på?

------------------
Motto: Flest If-taggar vinner!

Jo, att det finns index på de kolumner som man använder i joinvillkoren. (Det gäller oavsett antal tabeller man har men det blir en multiplikationseffekt.)

Då det blir flre möjliga accessvägar så ökar också risken att dbms väljer en dålig läsordning. Normalt så skall man börja i den tabell som förväntas ge lägst antal träffar för att joina den mängden med nästa tabell och så vidare. Det kan bli samma typ av multiplikatoreffekt här som om man saknar index. Det som är viktigt i detta sammanhang är att den statistik som samlas är aktuell.
Det finns sqlkommandon som create/update statistics (beroende på dbms) för att ordna detta.

En del dbms har också stöd för att man kan påverka i vilken ordning som tabellerna läses.

------------------
essentitia preter non sans multiplicandum