group by - Mysql find matching rows for common field in two tables and combine fields into one row -


i have 3 tables: table 'a' has number of events each unique id. table 'b' has categories match id of table though there several matches each id. table 'c' defines category names match id in table 'b'. how can output shows unique events have multiple categories listed opposed each event repeated per category?

       table                  table b              table c id | event | date |          id | catid             catid | cat 1     swim   1-2-16           1     11                11    slow 2     swim   1-2-16           1     12                12    med 3     run    1-3-16           1     13                13    fast 4     bike   1-5-16           2     11 5     run    1-30-16          3     12                               3     13                               4     12                               5     11  

i have mysql statement:

    select a.*, b.*, c.*          a, b, c          a.id = b.id , b.catid = c.catid , date(date) between "1-2-16" , "1-5-16 order event, cat  

problem output of statement repeats event each category. output list categories applicable each event output row. example, "swim on 1-2-16 slow,med,fast" instead of "swim on 1-2-16 slow" "swim on 1-2-16 med" "swim on 1-2-16 fast". how can accomplish this?

you should use group_concat

 select a.event, a,date, group_concat(c.cat)   inner join b on a.id = b.id  inner join b.catid = c.catid  group b.id; 

Comments

Popular posts from this blog

java - nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet Hibernate+SpringMVC -

sql - Postgresql tables exists, but getting "relation does not exist" when querying -

asp.net mvc - breakpoint on javascript in CSHTML? -