homepage Welcome to WebmasterWorld Guest from 54.234.141.47
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL Join Puzzle
enotalone

10+ Year Member



 
Msg#: 3416065 posted 6:36 pm on Aug 7, 2007 (gmt 0)

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

10+ Year Member



 
Msg#: 3416065 posted 6:59 pm on Aug 7, 2007 (gmt 0)

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

10+ Year Member



 
Msg#: 3416065 posted 12:39 pm on Aug 8, 2007 (gmt 0)

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

10+ Year Member



 
Msg#: 3416065 posted 7:01 pm on Aug 8, 2007 (gmt 0)

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

10+ Year Member



 
Msg#: 3416065 posted 7:20 pm on Aug 8, 2007 (gmt 0)

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

10+ Year Member



 
Msg#: 3416065 posted 7:25 pm on Aug 8, 2007 (gmt 0)

Thank you very much Syber.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved