Join multiple tables

[mustoora]

Hi there.
i'm having trouble joining tables. I'm using Access database. Initially, i have 2 tables called thresholdTable and Configuration. In the thresholdTable, there's a column called 'Config' and i've linked it to a column called 'Confuguration' from the Configuration table. Right now everything's working. However, i added another table, Consistency. I have a column in thresholdTable called 'Consistency' and i have to link it to 'Consistency' column from the Consistency table. How do i join 3 tables? Here's my codes:

Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As New OleDb.OleDbDataAdapter
Dim sql As String

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Sowmya.mdb;Jet OLEDB:Database Password=geez"

con.Open()

sql = "SELECT * FROM thresholdTable INNER JOIN Configuration ON Configuration.Configuration = thresholdTable.Config"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "Sowmya")

con.Close()

[Hamrick]

You can chain joins together and the next join works on the result of the last join.

SELECT *
FROM thresholdTable INNER JOIN Configuration
    ON Configuration.Configuration = thresholdTable.Config INNER JOIN Consistency
    ON Consistency.Consistency = thresholdTable.Consistency;

The intermediate table that you join Consistency to is the result of joining thresholdTable and Configuration. If you wrap it in parentheses it's like this guy.

((a INNER JOIN b ON a.col = b.col) INNER JOIN c ON c.col = a.col);

[mustoora]

I tried chain joins them together but i get an error. It says "Syntax error (missing operator) in query expression 'Configuration.Configuration = thresholdTable.Config INNER JOIN consistency ON consistency.consistency = thresholdTable.Consistency"

[QVeen72]

Hi,

check this:

[B]sql = "SELECT T.* FROM thresholdTable T, Configuration C Where  C.Configuration = T.Config"[/B]

Regards
Veena

[mustoora]

Then what about the consistency table? I also need to link the 'Consistency' column from thresholdTable to 'consistency' column from consistency table.

[QVeen72]

Hi,

Then give one more condition in where:

[B]sql = "SELECT T.* FROM thresholdTable T, Configuration C Where  C.Configuration = T.Config And C.Consistency = T.Consistency"[/B]

u can give any number of equal conditions..

Regards
Veena

[QVeen72]

Hi,

In case u have one more table, include that also in SQL Statement and give appropriate links..

Regards
Veena

[mustoora]

Hi..
I'm still facing some errors. It says No value given for one or more required parameters. By the way, my 'consistency' column is not from Configuration table. It's from another table called consistency. So actually i have 3 tables, thresholdTable, Configuration and consistency.

The relationship is like this:
Configuration.Configuration = thresholdTable.config,
consistency.consistency = thresholdTable.Consistency

[QVeen72]

Hi,

Post the whole SQL Statement here. Such error occur when u have Mis-Spelled the Column Or Table Name.
I noticed it now Remove the Comma in the Relationship and give AND there. Check This:

Where Configuration.Configuration = thresholdTable.config[B] And [/B]
consistency.consistency = thresholdTable.Consistency

[mustoora]

Hi..
i'm still having errors. I added this statement:

sql = "SELECT * FROM thresholdTable Where Configuration.Configuration = thresholdTable.config And consistency.consistency = thresholdTable.Consistency"

It highlighted this code and says that no value is given for one or more required parameters.

Me.ThresholdTableTableAdapter.Fill(Me.SowmyaDataSet.thresholdTable)

[mustoora]

By the way, i've checked the Column names and Table names. It is exactly what i've been using. So i don't know what went wrong.

[Hamrick]

Can you run the query straight in Access? You might get better error messages that what ADO.NET gives you.

[QVeen72]

Hello,

u cant give the thresholdTable name in Dataset like that...

Proper Syntax is:

Dim da As [B]OleDbDataAdapter[/B] = New [B]OleDbDataAdapter[/B](sql, conn) 
da.Fill (MydataSet,"ThrTable")

Regards
Veena

[Hamrick]

u cant give the thresholdTable name in Dataset like that...

I think mustoora is using a typed dataset. His code should be fine.

[QVeen72]

Hi,

ur SQL Statemnt should contain all the 3 tables names:

sql = "SELECT ThresoldTable.* FROM thresholdTable,Configuration,Consistency  Where Configuration.Configuration = thresholdTable.config And consistency.consistency = thresholdTable.Consistency"

Regards
Veena

[mustoora]

Hey thanks a lot for your help! Really appreciate it. I've managed to get it working. I used this code and it works:

sql = "SELECT * FROM thresholdTable, Configuration, consistency WHERE Configuration.Configuration = thresholdTable.config And consistency.consistency = thresholdTable.Consistency "

[QVeen72]

Hi,

then mark the thread as Solved....
Since it was not marked, i thought , ur problem is still hanging..

Regards
Vee