Trying to get mutliple inner joins in my sql-CodePudding

I'm using xampp with mysql and have been trying to format a query that uses inner joins on 4 seperate tables. The inner join contains the tables customer, bankaccounts, has and transactions. Has links the bank account and customer tables from M:N to 1:M while transactions is linked to bank accounts. I have attempted the following query:

SELECT t.TransactionID, 
       t.Description, 
       t.Amount, 
       t.isLoan, 
       t.TransactionDate, 
       a.AccountID, 
       a.SortCode, 
       a.Name, 
       a.CreationDate, 
       c.FirstName, 
       c.LastName 
       c.DateofBirth 
FROM transactions AS t 
INNER JOIN bankaccounts AS a

#inner join will create a new table by combining the values selected based on if they satisfy the on condition below

 ON t.TransactionID IN ( SELECT t.TransactionID 
                            FROM transactions 
                            WHERE t.TransactionDate BETWEEN '2021-11-25' AND '2021-12-01'
                          )
AND t.AccountID = a.AccountID
INNER JOIN has ON has.AccountID = a.AccountID  ---- > #multiple inner joins can occur on multiple tables
INNER JOIN customers AS c ON has.CustomerID = c.CustomerID;

However this currently only gives an error.

What I need is to link together all the tables while ensuring that only transactions between those specific dates are picked.

Is there any way to solve this?

CodePudding user response：

You should move the condition with the subquery to the WHERE clause:

SELECT t.TransactionID, t.Description, t.Amount, t.isLoan, t.TransactionDate, 
       a.AccountID, a.SortCode, a.Name, a.CreationDate, 
       c.FirstName, c.LastName, c.DateofBirth 
FROM transactions AS t 
INNER JOIN bankaccounts AS a ON t.AccountID = a.AccountID
INNER JOIN has ON has.AccountID = a.AccountID
INNER JOIN customers AS c ON has.CustomerID = c.CustomerID
WHERE t.TransactionDate BETWEEN '2021-11-25' AND '2021-12-01';