SQLITE INNERJOIN Nightmare, need a solution to this.

Question

Dear fellow members,

It's nice to find such a useful site with genius members. I have been trying to find a solution for this SQLITE problem for a while now. Google didn't help me, except in finding this website. The SQL query works fine on the MSAccess version of the same database.

Here's my SQL statement - which didn't work for me.

---

SELECT Invoices.InvoiceNumber, Invoices.Quantity,Invoices.Code, Invoices.Price,Invoices.Discount, Invoices.InvoiceGrandTotal, Employees.EmployeeName, Customers.CustomerName, Invoices.DateOfInvoice, [price]*[Quantity] AS Total, Customers.Address, Products.Description,Products.Unit  
    FROM Products 
        INNER JOIN (
            (   
                ( Invoices INNER JOIN InvoiceDetails 
                    ON Invoices.InvoiceNumber = InvoiceDetails.InvoiceNumber
                ) INNER JOIN Customers 
                    ON Invoices.CustomerID = Customers.CustomerID
            ) INNER JOIN Employees 
                ON Invoices.UserID = Employees.EmployeeID
        ) ON Products.Code = InvoiceDetails.Code  
    WHERE (((InvoiceDetails.InvoiceNumber)='10111'));

---

The error message is: "Cannot compile Select-Statement: no such column: Invoices.InvoiceNumber"

Answer 1

That usually just means that you mis-spelled the column name ... check your Invoices table and make sure the column is InvoiceNumber and not "Invoice_Number" or something similar ...

Also, a much simpler version of this query would look something like this .. without all the strange nesting:

SELECT 
   Invoices.InvoiceNumber, 
   Invoices.Quantity,
   Invoices.Code, 
   Invoices.Price,
   Invoices.Discount, 
   Invoices.InvoiceGrandTotal, 
   Employees.EmployeeName, 
   Customers.CustomerName, 
   Invoices.DateOfInvoice, 
   [price]*[Quantity] AS Total, 
   Customers.Address, 
   Products.Description,
   Products.Unit 
FROM
   Invoices

   JOIN Employees 
      ON Employees.EmployeeID = Invoices.UserID 

   JOIN Customers 
      ON Customers.CustomerID = Invoices.CustomerID

   JOIN InvoiceDetails 
      ON InvoiceDetails.InvoiceNumber = Invoices.InvoiceNumber

   JOIN Products
      ON Products.Code = InvoiceDetails.Code

WHERE 
   InvoiceDetails.InvoiceNumber = '10111'

Answer 2

I think the issue might be with case sensitivity. Unless I'm mistaken, MS Access field names are not case sensitive. Check the offending column name for the correct casing in you SQLITE table definition.