'IN' and 'INNER JOIN' keyword not working with database

Question

I am working on my senior project and we are using an embedded Apache Derby database. I am trying to query data from the the three tables below but having trouble.

 _________________ 
|Customers        | 
|-----------------|  
| *Customer_ID    |  
| LastName        |**
| FirstName       |**
| PhoneNumber     |  
| Street          | 
| City            |      
| State           |                        
| ZipCode         | 
| Note            |
 ----------------- 
 _________________
|Jobs             |
|-----------------|
| *Job_ID         |
| Customer_ID     |
| Address         |
| Date            |
 -----------------
 _________________
|Invoices         |
|-----------------|
| *Job_ID         |**
| Subtotal        |
| TaxPercent      |
| Total           |**
| DateSent        |**
| DateDue         |**
| DatePaid        |**
 -----------------

In the tables above, (**) refers to data that is needed to be be retrieved.

I believe that the query I have attached below should work but after executing, I get the an error message: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "IN";

If anyone could help out, thanks!

SELECT Customer_ID,
     , Job_ID
     , LastName
     , FirstName
     , Total
     , DateSent
     , DateDue
     , DatePaid
FROM Invoices
     , Customers
WHERE Customer_ID in (
                        SELECT Customer_ID
                        FROM Jobs 
                        WHERE Jobs.Job_ID in (
                                                SELECT Job_ID 
                                                FROM Invoices
                                             )
                     )

UPDATE: I switched to using MySQL for the application to see if that would solve the issue; it did not. I WAS able to get the following query to run in MySQL but I am still receiving errors when I execute the query from Java.

SELECT    Customers.Customer_ID
          ,Jobs.Job_ID
          ,LastName
          ,FirstName
          ,Total
          ,DateSent
          ,DateDue
          ,DatePaid
FROM Jobs INNER JOIN Customers on Customers.Customer_ID = Jobs.Customer_ID
          INNER JOIN Invoices on Jobs.Job_ID = Invoices.Job_ID;

What are the reasons that a query would run in MySQL but not in Java? Also selecting data from multiple tables without using INNER JOIN or IN works fine.

UPDATE2: I believe that I made a stupid mistake and did not leave a space between a keyword and an identifier. Thanks for the help!

Answer 1

I don't see anything wrong with your query, you can use JOIN instead of IN (it's faster in many cases)

SELECT j.Customer_ID,j.Job_ID,LastName,FirstName,Total, DateSent, DateDue,DatePaid
FROM Jobs as j inner join Customers as c on c.Curstomer_ID = j.CustomerID
               inner join Invoices as i on j.Job_ID = i.Job_ID