Special connection between 3 tables

Question

I created 3 mysql tables:

table 'city': contains cities

i.e   |  name  |
      +========+
      | Athens |
      +--------+
      | Rome   |
      +--------+
      | Paris  |
      +--------+

table 'category': contains categories

i.e   |    name    |
      +============+
      | category_1 |
      +------------+
      | category_2 |
      +------------+

table 'shops': contains shops

i.e   |  name  |  category_name  |  city_name  |
      +========+=================+=============+
      | shop_1 |   category_2    |    Rome     |
      +--------+-----------------+-------------|
      | shop_2 |   category_2    |    Rome     |
      +--------+-----------------+-------------+
      | shop_3 |   category_1    |    Paris    |
      +--------+-----------------+-------------+

I'm trying to create a single query that will allow me to create the following html array(that shows how many shops there are in each city grouped by category):

|        ||   category_1   |  category_2 |
+========++================+=============+
| Athens ||        0       |      0      |
+--------++----------------+-------------|
| Rome   ||        0       |      2      |
+--------++----------------+-------------+
| Paris  ||        1       |      0      |
+--------++----------------+-------------+

Thank you in advance, Giannis

possible duplicate of [MySQL dynamic pivot table](http://stackoverflow.com/questions/17773045/mysql-dynamic-pivot-table) — jpw, Aug 31 '15 at 12:20
If the number of categories isn't fixed then you need to use dynamic sql. The question flagged as duplicate shows you how. — jpw, Aug 31 '15 at 12:21

score 1 · Accepted Answer · answered Aug 31 '15 at 12:17

1

Try this

select c.name,
sum(case when s.category_name='category_1' then 1 else 0 end) as categoty_1,
sum(case when s.category_name='category_2' then 1 else 0 end) as categoty_2
from city as c left join shops as s on c.name=s,city_name
group by c.name

answered Aug 31 '15 at 12:17

Madhivanan

13,470
1
24
29

Thank you very much Madhivanan, it worked. Just a little correction: change "s,city_name" with "s.city_name" – yannis.tz Aug 31 '15 at 12:50

score 0 · Answer 2 · edited May 23 '17 at 10:27

0

Look into this SQL select data from multiple tables tables/32249225?noredirect=1#comment52417831_32249225

edited May 23 '17 at 10:27

Community

1
1

answered Aug 31 '15 at 12:19

Raj Kamuni

388
2
12

Thank you for your answer but it wasn't what i exactly wanted. Madhinavan gave me the correct answer – yannis.tz Aug 31 '15 at 12:52

score 0 · Answer 3 · edited Aug 31 '15 at 12:35

0

Try:

select Name, isnull([category_1],0), isnull([category_2],0) from
(
    select city.name as Name, category.name as Category, count(category.name) as   [Count]
    from city left join shops on shops.city_name = city.name
    left join category on category.name =  shops.category_name
    group by  city.name, category.name
) as PivotSource
pivot 
(
    sum([Count]) for Category in ([category_1], [category_2])
) as PivotTable

edited Aug 31 '15 at 12:35

Ameya Deshpande

3,580
4
30
46

answered Aug 31 '15 at 12:33

Teve

56
2

Hi Amey, thanks for your time. I try your query too but it didn't run. Mysql returns error #1064 – yannis.tz Sep 01 '15 at 15:21

Special connection between 3 tables

3 Answers3