Joining 3 Tables In SQL and Visual Basic

Question

    Public Class frmSelect

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ds As New DataSet
        Dim da As Odbc.OdbcDataAdapter = New Odbc.OdbcDataAdapter("SELECT tblStudInfo.LastName AS myName, tblAddress.Address AS myAddress, tblCourses.CourseName AS myCourse FROM tblStudInfo INNER JOIN tblAddress ON tblStudInfo.AddressID = tblAddress.AddressID INNER JOIN tblCourses ON tblStudInfo.CourseCode = tblCourses.CourseCode WHERE tblStudInfo.StudID = '01-2016-003'", mConnection.myConn)
        da.Fill(ds, "tblStudInfo")

        Me.TextBox1.Text = ds.Tables("tblStudInfo").Rows(0)("myName").ToString
        Me.TextBox2.Text = ds.Tables("tblStudInfo").Rows(0)("myAddress").ToString
        Me.TextBox3.Text = ds.Tables("tblStudInfo").Rows(0)("myCourse").ToString

    End Sub
End Class

I'm using Visual Basic... I have 3 tables in the Database. I am trying to join 3 tables and pass it to my Dataset named ds. But I got a Syntax Error with the SQL command I have provided. I got no error on joining 2 tables, but when I tried joining 3 tables with the code I have provided above, it is no longer working.

If you're using MS Access, extra parentheses are needed for the second join. — jarlh, Feb 17 '17 at 10:25
What is the error you are getting? Also what type is the tblsStudInfo.StudID in the database?? — Kostis, Feb 17 '17 at 10:34
@Kostis I am getting Syntax Error in SQL... tblStudInfo.StudID is Short Text. — Aaron, Feb 17 '17 at 10:36
@Kostis. My code was working when I was trying to join 2 tables... I got the error when I tried to join three tables. — Aaron, Feb 17 '17 at 10:37
Then you may need the extra parentheses as @jarlh mentioned. — Kostis, Feb 17 '17 at 10:41
Possible duplicate of [Ms-Access: Join 3 Tables](http://stackoverflow.com/questions/901935/ms-access-join-3-tables) — Bugs, Feb 17 '17 at 10:44
Have a look at the answers in that link @aer. They may help. — Bugs, Feb 17 '17 at 10:45
http://stackoverflow.com/questions/19367565/access-sql-inner-join-with-multiple-tables — Kostis, Feb 17 '17 at 10:45

score 0 · Accepted Answer · answered Feb 17 '17 at 10:48

Since 2 joins work and not 3, and that your SQL statement is syntaxically correct, I suspect that your backend DB is MS access. If it is the case, you should surround your JOINs with parenthesis :

SELECT tblStudInfo.LastName AS myName, tblAddress.Address AS myAddress, tblCourses.CourseName AS myCourse 
FROM ((tblStudInfo 
INNER JOIN tblAddress ON tblStudInfo.AddressID = tblAddress.AddressID )
INNER JOIN tblCourses ON tblStudInfo.CourseCode = tblCourses.CourseCode) 
WHERE tblStudInfo.StudID = '01-2016-003'

Joining 3 Tables In SQL and Visual Basic

1 Answers1