I want to write a nested join SQL query in SOQL but am not able to get it right after many attempts. Could someone please help out and explain what I'm getting wrong? TIA

SQL query:

```
SELECT O.ID, A.ID, A.AccountName, OP.Product2Name
FROM
[ORDER] O

JOIN
ACCOUNT A
    ON O.AccountID = A.ID

JOIN
    {
    SELECT P.ID, P.ProductName
        FROM
        ORDERITEM I
        JOIN
        PRODUCT2 P
        ON I.Product2ID = P.ID
    } AS OP
    ON OP.OrderID = O.OrderID

```

SOQL query I'm trying after all refinements:

```
SELECT ORDER.ID, ACCOUNT.ID, ACCOUNT.NAME, PRODUCT2.ID, PRODUCT2.NAME 
FROM ORDER, PRODUCT2, ACCOUNT, ORDERITEM
WHERE (ORDER.ID IN (SELECT ORDERID FROM ORDERITEM)) AND (PRODUCT2.ID IN 
(SELECT PRODUCT2ID FROM ORDERITEM)) AND (ACCOUNT.ID IN (SELECT ACCOUNTID IN ORDER))

```