Welcome to WebmasterWorld Guest from 54.196.175.173

Forum Moderators: open

Message Too Old, No Replies

MySQL Join Puzzle

     

enotalone

6:36 pm on Aug 7, 2007 (gmt 0)

10+ Year Member



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

6:59 pm on Aug 7, 2007 (gmt 0)

10+ Year Member



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

12:39 pm on Aug 8, 2007 (gmt 0)

10+ Year Member



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

7:01 pm on Aug 8, 2007 (gmt 0)

10+ Year Member



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

7:20 pm on Aug 8, 2007 (gmt 0)

10+ Year Member



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

7:25 pm on Aug 8, 2007 (gmt 0)

10+ Year Member



Thank you very much Syber.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month