Access-SQL:具有多个表的内部联接 [英] Access-SQL: Inner Join with multiple tables

问题描述

我在数据库中有多个表:

i have multiple tables in a database:

tblOjt

ID    studentid    courseid    companyid    addresseeid    dateadded    datestarted    dateended    ojthours

1         3            1           1             1         9/25/2013                                  500 

tblStudent

ID    lastname    firstname    middlename    course    gender    renderedhours    dateadded    archive

3     Dela Cruz      Juan        Santos       BSIT      Male          500

tblCourse

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

tblCompany

ID    companyname

1      MyCompany

tblAddressee

ID    addresseename

1     John dela Cruz

我需要有一条SQL语句，可以在其中获取以下值:

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

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

我将如何使用那些连接方法在SQL中获取此代码...将其用VB6 ADODC编写，这与标准SQL中的语法相同吗?谢谢

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

推荐答案

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

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

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

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.

原因是Access的联接语法一次仅支持联接两个表，因此，如果需要联接两个以上的表，则需要将多余的表括在括号中.

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-SQL:具有多个表的内部联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！