Kay Singian Kay Singian - 1 year ago 74
SQL Question

Access-SQL: Inner Join with multiple tables

i have multiple tables in a database:


ID studentid courseid companyid addresseeid dateadded datestarted dateended ojthours

1 3 1 1 1 9/25/2013 500


ID lastname firstname middlename course gender renderedhours dateadded archive

3 Dela Cruz Juan Santos BSIT Male 500


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


ID companyname

1 MyCompany


ID addresseename

1 John dela Cruz

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

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

Answer Source

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

, 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.

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download