I want to get the users(Gebruiker) that used their card(pas) for N amount of times between 2 dates.
Now I tried to use HAVING COUNT
or some variation of that, to get the amount of times a row contains a foreign key and use that to filter out everyone that used it atleast 5 times between 2 dates.
This is what I tried but without the date:
SELECT *, COUNT(Pas_pasnummer)
FROM gebruiker
INNER JOIN pas_has_poort ON gebruiker.gebruikernummer=pas_has_poort.Pas_pasnummer
HAVING COUNT(Pas_pasnummer) = '1'
CodePudding user response:
gebruiker.gebruikernummer
is not a foreign key to pas_has_poort.Pas_pasnummer
. You need to go through the linking table pas
.
You need to group the query by gebruikernummer
.
Then you can put a condition on the date in pas_has_poort
.
SELECT g.*, COUNT(*) AS use_count
FROM gebruiker AS g
JOIN pas AS p ON g.gebruikernummer = p.gebruikergebruikernummer
JOIN pas_has_poort AS pp ON p.pasnummer = pp.Pas_pasnummer
WHERE pp.inchecktijd BETWEEN @start_date AND @end_date
GROUP BY g.gebruikernummer
HAVING use_count >= 5