SQL forspørgsel mellem flere tabeller.

SELECT usergroup_name FROM forum_usergroup_useres, forum_usergroups WHERE forum_usergroup_users.usergroup_id=forum_usergroups.usergroup_id AND member_id=100

SELECT usergroup_name
FROM forum_usergroups
INNER JOIN forum_usergroup_users
ON forum_usergroups.usergroup_id = forum_usergroup_users.usergroup_id
WHERE forum_usergroup_users.member_ID = 100;

brugte korups svar.

men kan dette skrives endnu mere simple, til et udtræk-
SQL = "SELECT * FROM FORUM_MEMBERS WHERE M_NAME = '" & Session("Username") & "'"
Userinfo = Conn.Execute(SQL)
SQL = "SELECT usergroup_name FROM forum_usergroups INNER JOIN forum_usergroup_users ON forum_usergroups.usergroup_id = forum_usergroup_users.usergroup_id WHERE forum_usergroup_users.member_ID = " & Userinfo("MEMBER_ID")
UserGroup = Conn.Execute(SQL)
Session("UserInfo") = Session("Username") & "," & Userinfo("M_FIRSTNAME") & "," & Userinfo("M_LASTNAME") & "," & Userinfo("MEMBER_ID")  & "," & Usergroup("USERGROUP_NAME")

Samme princip:

SELECT forum_usergroups.member_id, usergroup_name FROM forum_usergroups INNER JOIN forum_usergroup_users ON forum_usergroups.usergroup_id = forum_usergroup_users.usergroup_id INNER JOIN forum_members ON forum_members.member_ID = forum_usergroups.member_id WHERE forum_members.member_name='" & Session("Username") & "'"

i det der kan jeg vel ikke få
Userinfo("M_FIRSTNAME")
Userinfo("M_LASTNAME")
ud ?
vel stadig kun
Usergroup("USERGROUP_NAME")`?

Ah, min fejl - fejllæste lige den første query.

Den rigtige query skal være:
SELECT forum_members.*, usergroup_name FROM forum_usergroups INNER JOIN forum_usergroup_users ON forum_usergroups.usergroup_id = forum_usergroup_users.usergroup_id INNER JOIN forum_members ON forum_members.member_ID = forum_usergroups.member_id WHERE forum_members.member_name='" & Session("Username") & "'"

Med den kan du sagtens få de ting, det er bare alt sammen i det samme resultatsæt.

Bare det er sku indviklet, men tak, burde kunne finde frem til at skrifte ud i den der fremover til at finde det jeg vil :)

SQL = "SELECT forum_members.*, usergroup_name FROM forum_usergroups INNER JOIN forum_usergroup_users ON forum_usergroups.usergroup_id = forum_usergroup_users.usergroup_id INNER JOIN forum_members ON forum_members.member_ID = forum_usergroups.member_id WHERE forum_members.member_name='" & Session("Username") & "'"
Userinfo = Conn.Execute(SQL)

icrosoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'forum_usergroups.usergroup_id = forum_usergroup_users.usergroup_id INNER JOIN forum_members ON forum_members.member_ID = forum_usergroups.member_id'.

ved ikke hvorfår jeg gav dig point, men det er forkert hvad du har givet mig.
forum_usergroups.member_id findes slet ikke i databasen, ved ikke lige hvor du har fået det fra.


SELECT USERGROUP_ID FROM FORUM_USERGROUP_USERS WHERE MEMBER_ID = 100

SELECT USERGROUP_NAME FROM FORUM_USERGROUPS WHERE USERGROUP_ID = resultatet ovenfor.

Ah, for pokker - der skulle naturligvis have stået forum_usergroups_users.member_id.

Nu kan jeg så se du bruger Access, og den er lidt speciel hvad SQL angår. Hvis du prøver at sætte lidt paranteser, skulle det virke (jf. http://www.codingforums.com/showthread.php?t=39008):

SELECT forum_members.*, usergroup_name FROM (forum_usergroups INNER JOIN forum_usergroup_users ON forum_usergroups.usergroup_id = forum_usergroup_users.usergroup_id) INNER JOIN forum_members ON forum_members.member_ID = forum_usergroups_users.member_id WHERE forum_members.member_name='" & Session("Username") & "'"

[Microsoft][ODBC Microsoft Access Driver] Syntax error in JOIN operation.

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
SELECT forum_members.*, usergroup_name FROM (forum_usergroups INNER JOIN forum_usergroup_users ON forum_usergroups.usergroup_id = forum_usergroup_users.usergroup_id) INNER JOIN forum_members ON forum_members.member_ID = forum_usergroup_users.member_id WHERE forum_members.member_name='" & Session("Username") & "'"

forum_usergroup_users.member_id du havde skrevet forum_usergroups_users.member_id

Godt så - nu har jeg taget den ved at gå ind i Access, lave samme tabelstruktur, og så få den til at generere en forespørgsel - det blev til følgende:

SELECT forum_usergroups.usergroup_name, forum_members.*
FROM forum_usergroups INNER JOIN (forum_members INNER JOIN forum_usergroup_users ON forum_members.member_id = forum_usergroup_users.member_id) ON forum_usergroups.usergroup_id = forum_usergroup_users.usergroup_id
WHERE (((forum_members.member_name)='navn'));

Indsæt selv din variabel på det rigtige sted.

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

hvordan får du acces til at lave den sql request

SQL = "SELECT FORUM_USERGROUPS.USERGROUP_NAME, FORUM_USERGROUPS.USERGROUP_ID, FORUM_MEMBERS.MEMBER_ID, FORUM_MEMBERS.M_FIRSTNAME, FORUM_MEMBERS.M_LASTNAME FROM (FORUM_USERGROUP_USERS INNER JOIN FORUM_USERGROUPS ON FORUM_USERGROUP_USERS.USERGROUP_ID = FORUM_USERGROUPS.USERGROUP_ID) INNER JOIN FORUM_MEMBERS ON FORUM_USERGROUP_USERS.MEMBER_ID = FORUM_MEMBERS.MEMBER_ID WHERE (((FORUM_MEMBERS.M_NAME)='" & Session("Username") & "'))"


VIRKER

Åbn din database, gå ind i Forespørgsler, og byg din query op der. Højreklik et tomt sted i vinduet, og vælg SQL-visning, så får du queryen at se.