What I need to do is to Count the Times a User has received the treatment ‘Oxygen’. I’m trying to do like below but I’m getting all of the entries from Treatment_Victim per user. Any help is well appreciated. Should I be using something else?
ID_Victim | Name |
---|---|
1 | John |
2 | Alex |
ID_Treatment | Name |
---|---|
1 | Oxygen |
2 | Transfussion |
ID_Treatment_Victim | ID_Victim | ID_Treatment |
---|---|---|
1 | 1 | 1 |
2 | 1 | 1 |
3 | 1 | 2 |
4 | 2 | 1 |
x
SELECT v.Name, count(tv.ID_Treatment_Victim) AS Times
FROM Victim v, Treatment t, Treatment_Victim tv
WHERE v.ID_Victim = tv.ID_Victim
AND tv.ID_Treatment = (SELECT ID_Treatment
FROM Treatment
WHERE Treatment.Nombre = 'Oxigen')
GROUP BY v.ID_Victim
ORDER BY Times DESC LIMIT 5;
Advertisement
Answer
Never use commas in the FROM
clause. Always use proper, explicit, standard, readable JOIN
syntax:
SELECT v.Name, COUNT(*) AS Times
FROM Treatment_Victim tv JOIN
Victim v
ON v.ID_Victim = tv.ID_Victim JOIN
Treatment t
ON v.ID_Treatment = tv.ID_Treatment
WHERE t.Nombre = 'Oxigen'
GROUP BY v.ID_Victim, v.Name
ORDER BY Times DESC
LIMIT 5;