Trying to get mutliple inner joins in my sql

Question

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?

Answer 1

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';