使用SQL在MS Access中创建表联接 [英] Using SQL to create table joins in MS Access

问题描述

我对SQL真的很陌生，除了最简单的联接(尤其是在MS Access中)之外，我真的在挣扎中挣扎.

在此阶段，我要做的就是从两个表中创建一个查询:"tblUsers"，其列为"UserID"和"User"，以及"tblPayments"，其列为"PaymentID"，"User"和"Authoriser" '.我希望我的查询包含所有这些数据，并具有"UserID"和"AuthoriserID"列，这两个ID号均取自"tblUsers"，但很明显其中一个与用户有关，一个与授权人有关. /p>

我确定这比我做的要简单得多，但是我应该怎么做呢?

谢谢.

解决方案

请记住，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;

这篇关于使用SQL在MS Access中创建表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！