I would like to join three tables with one condition per join (2 conditions) but I am having problems on the second join.

My tables (just a heading example) are:

-Table 1: clients

-Table 2: transactions

-Table 3: clients_history

And I would like to have a query that returns name and surname of those clients that have been registered between January 22 and March 22 (both included) AND the AVERAGE amount of their transactions is between 300 and 500.

I have this code but is not working:

SELECT name, surname
FROM (
    SELECT c.id, name, surname
    FROM clients c
    LEFT JOIN clients_history ch
    ON c.id = ch.client_id
    WHERE ch.action = "register" AND ch.date >= '2022-01-01' AND  ch.date<= '2022-03-31'
) AS r
LEFT JOIN transactions t
ON r.id = t.client_id
WHERE avg(t.amount) >= '300' AND  avg(t.amount)<= '500'
GROUP BY t.client_id 

Could you please help me?

Thanks!

CodePudding user response：

SELECT CL.CLIENT_ID,C.name,C.surname
FROM CLIENTS_HISTORY AS CL
JOIN CLIENTS AS C ON CL.client_id=C.id
JOIN 
(
  SELECT TR.client_id
  FROM TRANSACTIONS AS TR
  GROUP BY TR.client_id
  HAVING AVG(TR.AMOUNT)BETWEEN 300 AND 500
)X_TR ON C.id=X_TR.client_id
WHERE CL.action='register' AND CL.date>='2022-01-22' AND CL.date<='2022-03-22'

To test the query I amended registration data for client_id=1 to '2022-01-27'

CodePudding user response：

select c.name, c.surname, avg(t.amount)
from client c
left join transactions t
on c.id = t.client_id
left join client_history ch
on c.id = ch.client_id
where ch.date between "2022-01-01" and "2022-03-31"
and ch.action = "register"
group by c.name, c.surname
having avg(t.amount) between 300 and 500