Join three table in JDBC

Question

I have this code and keep getting a syntax error.

query = "SELECT * "+
"FROM Product " +
"INNER JOIN Orders ON (Product.ItemID=Orders.ItemID) " +
"LEFT OUTER JOIN SupplierProduct ON (Orders.ItemID=SupplierProduct.ItemID) "+
"WHERE Product.Receiver = 'Fred' " +
"ORDER BY Product.ItemName";    

DefaultTableModel data = table.getQuery(query);

I also have tried an INNER JOIN in place of the LEFT OUTER JOIN. The error I keep getting what ever I try is.

java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing     operator) in query expression '(Product.ItemID=Orders.ItemID) LEFT OUTER JOIN  SupplierProduct ON (Orders.ItemID=SupplierProduct.ItemID'.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)

What am I missing here or how can I JOIN three table.

Take a look at: http://stackoverflow.com/questions/10852499/access-2010-syntax-error-missing-operator-in-query-expression — Sotirios Delimanolis, Aug 08 '13 at 13:32

score 2 · Answer 1 · edited Jan 31 '16 at 21:14

2

Try using parenthesis like this:

query = "SELECT * "+
        "FROM (Product " +
        "INNER JOIN Orders ON (Product.ItemID=Orders.ItemID)) " +
        "LEFT OUTER JOIN SupplierProduct ON (Orders.ItemID=SupplierProduct.ItemID) "+
        "WHERE Product.Reciever = 'Fred' " +
        "ORDER BY Product.ItemName";

edited Jan 31 '16 at 21:14

marc_s

732,580
175
1,330
1,459

answered Aug 08 '13 at 13:41

BWS

3,786
18
25

Thanks you this did work for me. I did not know that brackets was need. – Themonkey180 Aug 08 '13 at 15:35

score 0 · Answer 2 · edited Feb 14 '16 at 21:40

It looks like JDBC may be having difficulties parsing the order of the joins. That is probably an issue specific to the database engine you are interacting with, but in any case, any engine should be able to parse the query correctly if you use parenthesis to specify the order of join operations:

query = "SELECT * "+
"FROM (Product " +
"INNER JOIN Orders ON (Product.ItemID=Orders.ItemID)) " +
"LEFT OUTER JOIN SupplierProduct ON (Orders.ItemID=SupplierProduct.ItemID) "+
"WHERE Product.Reciever = 'Fred' " +
"ORDER BY Product.ItemName";    

DefaultTableModel data = table.getQuery(query);

More details:

Basically, the idea is to segregate each join so that it can be interpreted more or less as a stand-alone table. This serves to more completely distinguish

SELECT * 
FROM (Table1 JOIN Table2 ON Table1.a = Table2.a) 
     JOIN Table3 on Table3.b = Table2.b

from

SELECT * 
FROM Table1 
     JOIN (Table2 JOIN Table3 on Table3.b = Table2.b) ON Table1.a = Table2.a

Also, note that there is a chance that you will need to alias the result of the first join, as in:

SELECT * 
FROM (Table1 JOIN Table2 ON Table1.a = Table2.a) as join1 
     JOIN Table3 on Table3.b = join1.b

Thank you for the details. Help me under stand it. – Themonkey180 Aug 08 '13 at 15:37 — Themonkey180, Aug 08 '13 at 15:37

Join three table in JDBC

2 Answers2