What is wrong with this SQL?

I have a line of SQL, which may not be the most efficient code, but it should work

select *, (Select Category from Categories where Categories.ID = Transactions.Category1) as CategoryName1, (Select Category from Categories where Categories.ID = Transactions.Category2) as CategoryName2, (Select Category from Categories where Categories.ID = Transactions.Category3) as CategoryName3 from Transactions Where ID = 1

So if I run this in my XOJO app from an SQLite database I get no rows returned. If I run it in SQLiteStudio UI I get no rows returned, but if I run it in SQLProSQLite UI I get the correct row returned.

Any idea what I am doing wrong?

I assume the query is wrong for SQLite in XOJO, so could you point me in the right direction ?

Thanks

Phil

I’m not sure, but try this instead:

SELECT
  t.*,
  c1.Category AS CategoryName1,
  c2.Category AS CategoryName2,
  c3.Category AS CategoryName3
FROM
  Transactions AS t
  INNER JOIN Categories AS c1 ON (c1.ID = t.Category1)
  INNER JOIN Categories AS c2 ON (c2.ID = t.Category2)
  INNER JOIN Categories AS c3 ON (c3.ID = t.Category3)
WHERE
  t.ID = 1

Also, if you use [ code ] … [ /code ] … (no spaces) around your coding statements, it will make posts easier to read.

My first instinct is that “transactions” is a reserved word.

transactions might not be the best name, but I don’t think it is reserved.

Greg is right, though, even if it is not a reserved word one should shy away from using any form of a reserved word. Besides, it’s generally better to be descriptive in names. What is it a transaction for? Maybe SaleTransactions or RegisterTransaction or ???Transaction would be a better name.

The same with Categories. What is it categorizing? Will you ever want to categorize anything else in your application? i.e. SaleCategories, PeopleCategories, TransactionCategories, etc…

Thanks guys - I will amend the naming used, but the code as posted still doesn’t return any data, so I am obviously doing something silly, but still can’t see what :frowning:

The Category1, 2 and 3 could all be different values as each “transaction” can belong to 3 different categories, or less. The joins are to look-up the name of the Categories that it is linked to. Just wondering if it is getting confused if one of these Category values is zero and therefore doesn’t exist in the Categories table? What would happen if one of these joins failed to return a value?

Phil

Phil,

I guess we will need to see more code. For example, here is a simple test I just did:

% sqlite3 hi.db
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE Categories (
   ...>   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   ...>   Category VARCHAR(40)
   ...> );
sqlite> CREATE TABLE Transactions (
   ...>   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   ...>   Amount DOUBLE,
   ...>   Category1 INTEGER REFERENCES Categories (ID),
   ...>   Category2 INTEGER REFERENCES Categories (ID),
   ...>   Category3 INTEGER REFERENCES Categories (ID)
   ...> );
sqlite> 
sqlite> INSERT INTO Categories (Category) VALUES ('Car'), ('Truck'), ('Van');
sqlite> INSERT INTO Transactions VALUES
   ...>   (1, 100, 1, 2, 2),
   ...>   (2, 5000, 3, 1, 2);
sqlite> 
sqlite> .headers on
sqlite> SELECT
   ...>   t.*,
   ...>   c1.Category AS CategoryName1,
   ...>   c2.Category AS CategoryName2,
   ...>   c3.Category AS CategoryName3
   ...> FROM
   ...>   Transactions AS t
   ...>   INNER JOIN Categories AS c1 ON (c1.ID = t.Category1)
   ...>   INNER JOIN Categories AS c2 ON (c2.ID = t.Category2)
   ...>   INNER JOIN Categories AS c3 ON (c3.ID = t.Category3)
   ...> WHERE
   ...>   t.ID = 1;
ID|Amount|Category1|Category2|Category3|CategoryName1|CategoryName2|CategoryName3
1|100.0|1|2|2|Car|Truck|Truck

Phil,

Sorry about that, I didn’t catch that some links may be 0 or EMPTY (thanks @Kem Tekinay). You will need to use a LEFT OUTER JOIN in that case. I have updated my example.

% sqlite3 hi.db
SQLite version 3.8.5 2014-08-15 22:37:57
Enter ".help" for usage hints.
sqlite> CREATE TABLE Categories (
   ...>   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   ...>   Category VARCHAR(40)
   ...> );
sqlite> CREATE TABLE Transactions (
   ...>   ID INTEGER PRIMARY KEY AUTOINCREMENT,
   ...>   Amount DOUBLE,
   ...>   Category1 INTEGER REFERENCES Categories (ID),
   ...>   Category2 INTEGER REFERENCES Categories (ID),
   ...>   Category3 INTEGER REFERENCES Categories (ID)
   ...> );
sqlite> 
sqlite> INSERT INTO Categories (Category) VALUES ('Car'), ('Truck'), ('Van');
sqlite> INSERT INTO Transactions VALUES
   ...>   (1, 100, 1, 2, 2),
   ...>   (2, 5000, 3, 1, 2),
   ...>   (3, 600, 0, 1, 2),
   ...>   (4, 800, NULL, NULL, 3);
sqlite> 
sqlite> .headers on
sqlite> SELECT
   ...>   t.*,
   ...>   c1.Category AS CategoryName1,
   ...>   c2.Category AS CategoryName2,
   ...>   c3.Category AS CategoryName3
   ...> FROM
   ...>   Transactions AS t
   ...>   LEFT OUTER JOIN Categories AS c1 ON (c1.ID = t.Category1)
   ...>   LEFT OUTER JOIN Categories AS c2 ON (c2.ID = t.Category2)
   ...>   LEFT OUTER JOIN Categories AS c3 ON (c3.ID = t.Category3);
ID|Amount|Category1|Category2|Category3|CategoryName1|CategoryName2|CategoryName3
1|100.0|1|2|2|Car|Truck|Truck
2|5000.0|3|1|2|Van|Car|Truck
3|600.0|0|1|2||Car|Truck
4|800.0|||3|||Van