How do I get a 4 table inner join to work (VB.NET)

Question

3.00/5 (1 vote)

See more:

As of right now I can get 3 tables to merge using an inner join, but I can not seem to find a way to get 4+ tables inner joined.

Here is my code:

Dim sqlcon As New SqlConnection("sqlstring")
     Dim sqladapt As New SqlDataAdapter("Select * From profile inner join (cemeteries inner join cemetery_reference on cemeteries.Cemetery_ID = cemetery_reference.Cemetery_ID)  on Profile.Individual_ID = Cemeteries.Individual_ID", sqlcon)

     sqlcon.Open()
     sqladapt.SelectCommand.ExecuteNonQuery()

     Dim DT As New DataTable
     sqladapt.Fill(DT)
     DataGridView1.DataSource = DT
     sqlcon.Close()

I keep getting "incorrect syntax on"

What I have tried:

I have tried double parenthesis after the word from, however, I am still getting the same syntax error.

Posted 13-Sep-18 16:41pm

Member 11856456

Updated 13-Sep-18 22:24pm

Add a Solution

Comments

Maciej Los 14-Sep-18 3:45am

What's DBMS?

2 solutions

Solution 1

you should try your SQL in SqlManagement, for faster SQL debugging! ;)
At any rate I immediatelyu spoted one bu (there might be more ...)

Select * From profile inner join (cemeteries inner join ...
should be, instead
Select * From profile inner join (select * from cemeteries inner join ...

Posted 13-Sep-18 17:35pm

Super Lloyd

Comments

Member 11856456 13-Sep-18 23:40pm

this is for a 3 table, but what about for a 4 table join?

CHill60 14-Sep-18 8:01am

A sub-query? Why would you do that? Don't get confused by the brackets (which are required by Access)

Member 11856456 14-Sep-18 14:10pm

actually watching a video on a 3 table inner join brought me to an access video. I followed the example and it worked, but could not get this to run with a 4 table. However, the example above helped and I see where my mistake was.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-09-13T22:24:00

You didn't provide information about DBMS. Nevertheless...
The number of tables available to join within single query depends on DBMS...

A "typical" query looks like:

SQL

SELECT A.<List_Of_Fields>, B.<List_Of_Fields>, C.<List_Of_Fields>, D.<List_Of_Fields>
FROM Table1 AS A
    INNER JOIN Table2 AS B ON A.PrimaryKey = B.ForeignKey
    INNER JOIN Table3 AS C ON B.PrimaryKey = C.ForeignKey
    INNER JOIN Table4 AS D ON C.PrimaryKey = D.ForeignKey

There's few types of joins. See: Visual Representation of SQL Joins[^]

For further details, please see:
MS SQL Server: Querying with Joins[^]
MS Access: Join tables and queries - Access[^]