访问sql:内部连接和多个表。

i have multiple tables in a database:

我在一个数据库中有多个表:

tblOjt

tblOjt

ID    studentid    courseid    companyid    addresseeid    dateadded    datestarted    dateended    ojthours

1         3            1           1             1         9/25/2013                                  500

tblStudent

tblStudent

ID    lastname    firstname    middlename    course    gender    renderedhours    dateadded    archive

3     Dela Cruz      Juan        Santos       BSIT      Male          500

tblCourse

tblCourse

ID    coursealias    coursename                                            hours
1         BSIT      Bachelor of Science in Information Technology          500

tblCompany

tblCompany

ID    companyname

1      MyCompany

tblAddressee

tblAddressee

ID    addresseename

1     John dela Cruz

i need to have a SQL statement in which i can get this values:

我需要有一个SQL语句，在其中我可以得到这个值:

tableOjt.id  tableOJT.surname,firstname, and middlename  course  companyname  addresseename dateadded datestarted dateended ojthours

how will i get this code in SQL using those join methods...im writing it in VB6 ADODC, is this the same syntax in a standard SQL ? thanks

如何使用这些连接方法在SQL中获取这些代码……我用VB6 ADODC写，这是标准SQL中的相同语法吗?谢谢

3 个解决方案

#1

If you are writing a query against an Access database backend, you need to use the following join syntax:

如果要针对访问数据库后端编写查询，需要使用以下连接语法:

select
  t1.c1
, t2.c2
, t3.c3
, t4.c4
from ((t1
inner join t2 on t1.something = t2.something)
inner join t3 on t2.something = t3.something)
inner join t4 on t3.something = t4.something

The table and column names aren't important here, but the placement of the parentheses is. Basically, you need to have n - 2 left parentheses after the from clause and one right parenthesis before the start of each new join clause except for the first, where n is the number of tables being joined together.

表和列名在这里不重要，但括号的位置很重要。基本上，您需要在from子句后面有n - 2个左括号，在每个新join子句开始之前有一个右括号，除了第一个，其中n是连接在一起的表的数量。

The reason is that Access's join syntax supports joining only two tables at a time, so if you need to join more than two you need to enclose the extra ones in parentheses.

原因是Access的连接语法一次只支持连接两个表，因此如果需要连接两个以上的表，需要将额外的表括在括号中。

#2

SELECT tblOjt.id, tblStudent.firstname, tblStudent.middlename, 
       tblStudent.lastname, tblStudent.course, tblCompany.companyname, 
       tblAddressee.addressee 
FROM (((tblOjt 
     INNER JOIN tblStudent ON tblOjt.studentid = tblStudent.id) 
     INNER JOIN tblCourse ON tblOjt.courseid = tblCourse.id) 
     INNER JOIN tblCompany ON tblOjt.companyid = tblCompany.id) 
     INNER JOIN tblAddressee ON tblOjt.addresseeid = tbladdressee.id

found it!thanks to Yawar's approach...

发现它!由于Yawar的方法……

#3

been trying to run this SQL using VBA but won't run using DoCmd.RunQuery. I've tried the SQL and it's working though.

一直在尝试使用VBA运行这个SQL，但是不会使用DoCmd.RunQuery。我已经尝试过SQL，它正在工作。

str = "SELECT tbl_company.[Company], tbl_company.[Commodity], tbl_company.[Segment], tbl_company.[MainProduct]," & _
      " tbl_financials.[DataYear]," & _
      " mstr_financial.[FinancialData]," & _
      " tbl_financials.[Amount]," & _
      " tbl_financials.[Unit]," & _
      " tbl_company.[CompanyID]" & _
      " FROM (tbl_company" & _
      " INNER JOIN tbl_financials ON tbl_company.[CompanyID] = tbl_financials.[CompanyID])" & _
      " INNER JOIN mstr_financial ON tbl_financials.[FinID] = mstr_financial.[FinID] " & _
      " ORDER BY tbl_company.[Company], tbl_financials.[DataYear] DESC"

#1