更新时间:2022-11-27 20:38:32
请记住,SQL Server SQL和Access SQL的语法略有不同.尽管在大多数情况下,用Access编写的查询将在SQL Server上正常运行,但事实并非如此,因为在SQL Server上经常使用快捷方式(例如:在使用别名时从联接中删除"INNER",在使用别名时删除"AS" )
访问权限:
SELECT tblUsers.UserID,
tblUsers.User,
tblPayments.paymentID,
tblUsers.User AS Authoriser,
tblUsers_1.UserID AS AuthoriserID
FROM (tblPayments
INNER JOIN tblUsers AS tblUsers_1
ON tblPayments.AuthorisorID = tblUsers_1.UserID)
INNER JOIN tblUsers
ON tblPayments.userID = tblUsers.UserID;
SQL Server:
SELECT tblUsers.UserID,
tblUsers.User,
tblPayments.paymentID,
tblUsers.User Authoriser,
tblUsers_1.UserID AuthoriserID
FROM tblPayments
JOIN tblUsers tblUsers_1
ON tblPayments.AuthorisorID = tblUsers_1.UserID
JOIN tblUsers
ON tblPayments.userID = tblUsers.UserID;
I'm REALLY new to SQL and I'm really struggling with all but the simplest of joins - especially in MS Access.
At this stage, all I want to do is create a query from two tables: 'tblUsers', with columns 'UserID' and 'User', and 'tblPayments' with columns 'PaymentID', 'User' and 'Authoriser'. I want my query to contain all of this data and also to have columns 'UserID' and 'AuthoriserID', both of these ID numbers being taken from 'tblUsers', but clearly one will relate to the User and one to the authoriser.
I'm sure this is far more simple than I'm making it but how should I do this?
Thanks in advance.
Keep in mind that there are minor differences in syntax between SQL server SQL and Access SQL. While in most cases a query written in Access will function normally on SQL server, the opposite is less true because of the shortcuts often used on SQL server (example: dropping the 'INNER' from the joins and the 'AS' when using an alias.)
Access:
SELECT tblUsers.UserID,
tblUsers.User,
tblPayments.paymentID,
tblUsers.User AS Authoriser,
tblUsers_1.UserID AS AuthoriserID
FROM (tblPayments
INNER JOIN tblUsers AS tblUsers_1
ON tblPayments.AuthorisorID = tblUsers_1.UserID)
INNER JOIN tblUsers
ON tblPayments.userID = tblUsers.UserID;
SQL Server:
SELECT tblUsers.UserID,
tblUsers.User,
tblPayments.paymentID,
tblUsers.User Authoriser,
tblUsers_1.UserID AuthoriserID
FROM tblPayments
JOIN tblUsers tblUsers_1
ON tblPayments.AuthorisorID = tblUsers_1.UserID
JOIN tblUsers
ON tblPayments.userID = tblUsers.UserID;