Join 3 tables with 2 where

gresell 0
Viewed 46

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 are:

clients

id name surname
1 Lois Smitm
2 Mike Williams
3 Jonh Brown
4 Dustin Jones
5 Robert Johnson

transactions

id client_id date amount
1 1 2022-12-10 350
2 2 2022-12-11 350
3 3 2022-12-12 210
4 4 2022-12-13 200
5 5 2022-12-14 250
6 1 2022-12-15 450
7 5 2022-12-16 850

clients_history

id client_id action date
1 1 register 2022-01-20
2 2 register 2022-01-21
3 3 register 2022-06-19
4 4 register 2022-03-10
5 5 register 2022-02-20

I would like 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 it 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
where-clause average join mysql sql
2 Answers
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'

userrnZY0 
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
Zaid Parkar0
Related