Selecting Items from another table using ID

I have two tables Item and a table which records the items in each order (Junction Table)

ITEM Table

String itemTable = "CREATE TABLE " + ITEM_TABLE + " ("
        + ID_ITEM + " INTEGER PRIMARY KEY AUTOINCREMENT,"
        + ITEM_NAME + " TEXT,"
        + ITEM_TYPE + " TEXT,"
        + ITEM_PRICE + " TEXT);";

ORDER_ITEM Table

String orderItemTable = "CREATE TABLE " + ORDER_ITEM_TABLE + " ("
        + ID_ORDER_ITEM + " INTEGER,"
        + ID_ITEM_ORDER + " INTEGER,"
        + " FOREIGN KEY ("+ID_ORDER_ITEM+") REFERENCES "+ EMP_TABLE +"("+ ID_EMP +"), "
        + " FOREIGN KEY ("+ID_ITEM_ORDER+") REFERENCES "+ EMP_TABLE +"("+ ID_EMP +"));";

Data in the ORDER_ITEM table each Item id refers to a specific item in the item table this is what I am trying to refer to and use to pull data from the DB.

At the moment I can pull the Item id from this table but not the actual Item using the Id. Here idOrder is passed when the order is selected

String selctAllEmployeesOrdersItems = "SELECT * FROM " + ORDER_ITEM_TABLE + " WHERE " + ID_ORDER_ITEM + " = " + idOrder;

One way of achieving what I want is by storing the Item ID's in a ArrayList and iterating over this to select all of the Items but I know there is a better way.

Some research I have seen has suggested that I join the tables referencing the ITEM ID but I am not sure of the correct syntax. The dot notation does not work with this query.

String selctAllEmployeesOrdersItems = "SELECT * FROM " + ORDER_ITEM_TABLE + " WHERE " + ID_ORDER_ITEM + " = " + idOrder
        + " JOIN " + ITEM_TABLE + " ON " + ORDER_ITEM_TABLE+"."+ID_ITEM_ORDER + " = " + ITEM_TABLE+"."+ID_ITEM;

The error message being show is

> Caused by: android.database.sqlite.SQLiteException: near "JOIN":
> syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT * FROM
> ORDER_ITEM_TABLE WHERE ID_Order = 1 JOIN ITEM_TABLE ON
> ORDER_ITEM_TABLE.ID_Item = ITEM_TABLE.ID

The syntax of your SQL statement is wrong.<br/>
The WHERE clause must be written after the join:

String selctAllEmployeesOrdersItems = 
  "SELECT * FROM " + ORDER_ITEM_TABLE + " AS o " + 
  "INNER JOIN " + ITEM_TABLE + " AS i ON o." + ID_ITEM_ORDER + " = i." + ID_ITEM + " " +
  "WHERE o." + ID_ORDER_ITEM + " = " + idOrder;

Note the use of aliases o and i for the 2 tables that shortens significantly the code.<br/>

Also, the definition of the table ORDER_ITEM:

String orderItemTable = "CREATE TABLE " + ORDER_ITEM_TABLE + " ("
        + ID_ORDER_ITEM + " INTEGER,"
        + ID_ITEM_ORDER + " INTEGER,"
        + " FOREIGN KEY ("+ID_ORDER_ITEM+") REFERENCES "+ EMP_TABLE +"("+ ID_EMP +"), "
        + " FOREIGN KEY ("+ID_ITEM_ORDER+") REFERENCES "+ EMP_TABLE +"("+ ID_EMP +"));";

does not seem correct.<br/>
What is the table EMP_TABLE?<br/>
Why do both columns ID_ORDER_ITEM and ID_ITEM_ORDER reference the same column?<br/>
This does not make sense.

I believe you have the clauses in the wrong order, this should be:
select ... from ... join ... on ... where ...
So in this case:

SELECT * FROM
ORDER_ITEM_TABLE JOIN ITEM_TABLE
ON ORDER_ITEM_TABLE.ID_Item = ITEM_TABLE.ID
WHERE ORDER_ITEM_TABLE.ID_Order=1;