MySQL Join Puzzle

[enotalone]

I am little puzzled with this, if anyone can think of a way I can join the 3 tables would appreciate it. Thanks.

Table A: items

iid (primary key)
store_id
sale_avg (integer)

Table B: store

store_id (primary key)
store_name

Table C: store_location

locit (primary key)
store_id
address
city
state
zip

1 store can have many locations in Table C (store_location) and more than 1 item in table A (items)

What I am trying to accomplish is to create a sql query that would join the 3 tables to present top 10 cities with most sales.

sale_avg field in table A has this value, the challenge is to somehow join it with table C (store_location) and get the average for top 10 cities.

Thank you.

[enotalone]

the closest I got is the query below. But! I am not sure that by grouping on the city I actually get the correct results and it uses temporary tables and filesort. So even if it works right that is one slow and resource intensive query.

SELECT city, state, avg( sale_avg ) AS save FROM store_location
LEFT JOIN item ON (item.store_id = store_location.store_id )
GROUP BY city
ORDER BY save DESC

[syber]

Since you are doing a GROUP BY city, you don't need to join the store table. It also should not be necessary to do a LEFT JOIN as there should be a store_location for every item.

SELECT city, state, avg( sale_avg ) AS save
FROM store_location INNER JOIN item
ON (item.store_id = store_location.store_id )
GROUP BY city
ORDER BY save DESC

[enotalone]

Thanks syber, that seems to do the job.

Anything I can do to optimize the query so there are no temporary tebles, filsort when the query is executed?

[syber]

I did notice one problem, you should be grouping on city, state - not just city.

SELECT city, state, avg( sale_avg ) AS save
FROM store_location INNER JOIN item
ON (item.store_id = store_location.store_id )
GROUP BY city, state
ORDER BY save DESC

This shouldn't cause any temp tables and is the most efficent solution. Having an index on city,state will probably speed the query up.

[enotalone]

Thank you very much Syber.