CODEWITHSUNDEEP
javasqlderby
SaB
SaB

Reputation: 787

How to join three tables in Apache Derby Database?

Question:

What syntax do I need to use to simply join three tables using Apache Derby?

What I have tried:

I am converting a Java application from SQLite to Apache Derby. In SQLite, the following SQL syntax works fine.

sql.append("SELECT MatterDataset.id,  ");
//Removed rest of selection items for readability and to focus the inquiry

sql.append(" FROM MatterDataset, DocumentClassification, PrivilegeLog  " ) ; //<--PROBLEM LINE
sql.append("  ON MatterDataset.matterid = DocumentClassification.matterid  " ) ;
sql.append("   AND MatterDataset.matterid = PrivilegeLog.matterid   " ) ;
sql.append("   AND MatterDataset.id = DocumentClassification.documentid   " ) ;
sql.append("   AND MatterDataset.id = PrivilegeLog.documentparentid    " ) ;
sql.append(" WHERE    " ) ;
sql.append("  Matterdataset.matterid = ? " ) ; //Prepared statement
sql.append("  AND Matterdataset.isdeleted = 0  " ) ;

The line causing problems with the conversion to Apache Derby is:

sql.append(" FROM MatterDataset, DocumentClassification, PrivilegeLog  " ) ;

I have tried several variations and all raise syntax errors with Java Derby (via the ij tool).

1) SYNTAX ERROR at the ',' but this syntax works with only two tables

sql.append(" FROM MatterDataset JOIN DocumentClassification, PrivilegeLog  " ) ;

2) Direct as above and SYNTAX ERROR at the ","

sql.append(" FROM MatterDataset, DocumentClassification, PrivilegeLog  " ) ; //

3) SYNTAX ERROR at the "AND"

sql.append(" FROM MatterDataset JOIN DocumentClassification AND PrivilegeLog  " ) ; //

I am not sure what else to try (after performing a web search for similar issues with SQL in general). Is there a way to do this simply in Apache Derby?

Environment Setup:

Java 7
Apache Derby 10.9.1.

Upvotes: 1

Views: 3707

Answers (2)

Bryan Pendleton
Bryan Pendleton

Reputation: 16349

I think that you were trying to mix two different styles of join syntax.

Using ON syntax to specify the join conditions is a different style than using WHERE syntax.

When you use ON syntax, your SELECT statement should look like:

SELECT columns FROM T1 INNER JOIN T2 ON t1-t2 join conditions JOIN T3 ON ...

But when you use a comma separated table list, you don't use ON clauses, you just lump all the join conditions into the WHERE clause.

So don't mix the two styles, just use one or the other.

By the way, the older style that you chose is great for INNER join, but can't be used for OUTER join, so it's generally the need to specify an OUTER join that causes programmers to switch to the ON clause style.

But you can use ON clause for INNER joins too, just don't give a comma-separated table list.

Upvotes: 1

user2173738
user2173738

Reputation:

You can join tables simply with ,

sql.append("SELECT MatterDataset.id  ");
//Removed rest of selection items for readability and to focus the inquiry

sql.append(" FROM MatterDataset, DocumentClassification, PrivilegeLog  " ) ; //<--NOT A PROBLEM LINE
sql.append(" WHERE    " ) ;
sql.append("   MatterDataset.id = DocumentClassification.matterid  " ) ;
sql.append("   AND MatterDataset.id = PrivilegeLog.matterid   " ) ;
sql.append("   AND DocumentClassification.id = PrivilegeLog.documentparentid    " ) ;
sql.append("   AND Matterdataset.id = ? " ) ; //Prepared statement
sql.append("   AND Matterdataset.isdeleted = 0  " ) ;

The Derby database also could be optimized for performance.

Upvotes: 2

Related Questions