perksy
-
Posts
2 -
Joined
-
Last visited
Posts posted by perksy
-
-
Hi everyone,
I've been wrapping my head around this problem and I was wondering if you could help me.
I have the following three tables with example data:
results_patterns
===========================
name | medal
-------- | ------------
James | Gold
James | Silver
James | Silver
Paul | Gold
Sam | Bronze
Sarah | Silver
results_sparring
===========================
name | medal
-------- | ------------
James | Bronze
James | Bronze
James | Bronze
Paul | Silver
Sam | Gold
Sarah | Silver
results_patterns
===========================
name | medal
-------- | ------------
James | Silver
James | Silver
James | Gold
Paul | Gold
Sam | Bronze
Sarah | Silver
There are other columns in the tables such as date, age, category etc but these are not necessary. The idea being that there can be duplicate medals and names in each table (due to different competition dates)
I'm trying to get a medal count for each individual person, and produce an sql statement to get a result like this:
results
===========================
name | gold | silver | bronze
---------|----------|-----------|-----------
James | 2 | 4 | 3
Paul | 2 | 1 | 0
Sam | 1 | 0 | 2
Sarah | 0 | 3 | 0
I hope it's clear what I'm after; I'll happily explain more if required.
I've tried UNION ALL three tables for WHERE 'Gold' medals. I wanted to count how many gold medals per person but I failed at this stage. I was then going to perhaps JOINT LEFT this results with the same for each Silver and Bronze. But I have no idea how to go about it.
Any help would be greatly appreciated as I'm a noob with MySQL.
Thanks very much
Counting Medals Across Different Tables Unique To Each Person
in MySQL Help
Posted
Wow! Thanks so much for your help, it works flawlessly! It's so much simpler than I thought it was going to be as well. I'm now going to do a bit more reading about what is exactly going on but I think I get it.
Thanks once again