Search the Community

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