Syntax for inner Join query for 4 table

Question

I want to get the data from my access database and show the data at Datagridview. my tables are:

TABLE 1(AB)           TABLE 2(CD)         TABLE 3(EF)      TABLE 4(GH)
-------------        -----------------   -------------    ------------------- 
SID SName CID TID    TID TName Tprice     FID CID FCp      FPID FID FCp Fprice

The query which I am using to retrieve the data in C# is:

OleDbCommand command1 = new OleDbCommand();
command1.Connection = connection;
command1.CommandText = "select T.TName, T.Tprice, P.FCp, P.Fprice from (([AB] S inner join [CD] T on S.TID = T.TID) (inner join [EF] C on S.CID = C.CID) inner join [GH] P on C.FID = P.FID where (S.SID = 2) ";
OleDbDataReader myreader = command1.ExecuteReader();
while (myreader.Read())
{
    //DATA IS READ HERE
}

The error which I am getting is:

Syntax error at JOIN expression

I want TName, Tprice, FCp(TABLE 4), Fprice as my output. Am I doing it right or is there any other way to do that.

score 4 · Accepted Answer · edited Jul 30 '17 at 08:46

4

In your from you have opening parentheses with no closing (the first one).

Besides fixing the parentheses it is much more readable if you jump lines:

command1.CommandText = @"select T.TName, T.Tprice, P.FCp, P.Fprice 
                         from (([AB] S 
                         inner join [CD] T on S.TID = T.TID)
                         inner join [EF] C on S.CID = C.CID) 
                         inner join [GH] P on C.FID = P.FID
                         where S.SID = 2";

(Access requires parentheseses)

edited Jul 30 '17 at 08:46

Nitish Ranjan

45
5

answered Jul 30 '17 at 08:05

Gilad Green

36,708
7
61
95

now i am getting error Syntax error (missing operator) in query expression – Nitish Ranjan Jul 30 '17 at 08:21
@NitishRanjan - see update - missed that access requires adding the parentheses – Gilad Green Jul 30 '17 at 08:26
small change in query brackets should start after 'from' not before – Nitish Ranjan Jul 30 '17 at 08:40

Syntax for inner Join query for 4 table

1 Answers1