Access-SQL: Inner Join with multiple tables

ghz 7months ago ⋅ 54 views

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

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

Answers

To retrieve the desired values from your database using SQL with JOIN methods, you can use the following SQL query. This query assumes that you are using a standard SQL database like MySQL, PostgreSQL, or SQL Server. The syntax might slightly differ based on the specific SQL database you are using, but the core structure remains the same.

Here's the SQL query to achieve the desired result:

SELECT 
    tblOjt.ID, 
    tblStudent.lastname, 
    tblStudent.firstname, 
    tblStudent.middlename, 
    tblCourse.coursealias AS course, 
    tblCompany.companyname, 
    tblAddressee.addresseename, 
    tblOjt.dateadded, 
    tblOjt.datestarted, 
    tblOjt.dateended, 
    tblOjt.ojthours
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;

Explanation:

  • SELECT ...: Specifies the columns you want to retrieve.
  • FROM tblOjt: Specifies the main table from which to retrieve the data.
  • INNER JOIN ... ON ...: Joins other tables based on the related columns.

In the context of VB6 ADODC (ActiveX Data Objects Data Control), the SQL query remains the same, but you would assign this SQL query to the RecordSource property of the ADODC control. Here’s an example of how you can do it in VB6:

Dim sqlQuery As String

sqlQuery = "SELECT tblOjt.ID, tblStudent.lastname, tblStudent.firstname, tblStudent.middlename, " & _
           "tblCourse.coursealias AS course, tblCompany.companyname, tblAddressee.addresseename, " & _
           "tblOjt.dateadded, tblOjt.datestarted, tblOjt.dateended, tblOjt.ojthours " & _
           "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"

ADODC1.RecordSource = sqlQuery
ADODC1.Refresh

In this VB6 code snippet:

  • sqlQuery contains the SQL query.
  • ADODC1.RecordSource is assigned the SQL query string.
  • ADODC1.Refresh is called to execute the query and populate the control with the data.

Make sure your ADODC control is properly set up with the correct connection string to your database.

#vba