mysql - Joining two tables to count and list and sort -

table1: keywords

itemid  kwtype   kwvalue  1       brand     nike 2       brand     nike 1       type      soccer 4       brand     adidas 

table2: items

itemid  siteid  categoryid  itemname  1        0         10         shoe1 2        0         10         shoe2 3        2         55         shoe3 4        1         11         shoe4 

i have these 2 tables (who of course lot bigger)

how proceed know 10 occurring brands in site = 0 , category = 10? want list top 10 (top 10 occurring brands) in 1 column , in other column want count how many times appear

thanks

first of all, should adjust data model. work lot more efficiently , easier understand

table: brands

id  itemid  brandname   1       1  nike  2       2  nike  3       4  adidas 

table: types

id  itemid  typename   1       1  soccer 

table: items

id  siteid  categoryid  itemname   1       0          10  shoe1  2       0          10  shoe2  3       2          55  shoe3  4       1          11  shoe4 

build foreign keys link itemid in brands , types tables id in items table.

then can run query this

select brandname brand, count(brand.id) qty brands left join items on items.id = brand.itemid siteid = 0 , categoryid = 10 group brandname order qty desc limit 10