homepage Welcome to WebmasterWorld Guest from 54.161.236.92
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Complex MySQL Order BY Group
Can anyone help? :)
wfernley




msg:3808066
 7:35 pm on Dec 15, 2008 (gmt 0)

Hi everyone,

I have a large product database and I need to add ordering so users can order by product name or price. The hard part is each product has multiple price options so the sql command needs to grab the lowest price option of each product and order by that without providing any duplicates

Here is my SQL command:

SELECT * FROM fs_products, fs_products_to_categories, fs_products_pricing WHERE fs_products_to_categories.categories_id = 255 AND fs_products.products_id = fs_products_to_categories.products_id AND fs_products_pricing.products_id = fs_products.products_id AND fs_products.products_visibility = 1 GROUP BY fs_products.products_part_number ORDER BY fs_products_pricing.pricing_price LIMIT 0, 10

It is pulling data from three tables....

First as you can see it looks to grab the products in the selected category then it grabs the pricing for that product and tries to group them considering I don't want it to print the same product over and over again (considering there are multiple prices)

Unfortunately the command is not working....I don't even get an error it just seems to take forever. There are about 120,000 products each having 2-3 price options.

Can anyone help? I can provide additional details if neccessary :)

Thanks in advance for your help!

Wes

 

topr8




msg:3808513
 12:22 pm on Dec 16, 2008 (gmt 0)

you are selecting * (all)

but you are only grouping by products_part_number

you need to group by every field you are selecting.

...

but i'm not sure this query will pull the data you are expecting, although it is hard to tell as i'm making assumptions about your tables which could be wrong.

wfernley




msg:3808526
 12:45 pm on Dec 16, 2008 (gmt 0)

I can't select by * then?

Will I need to specify each cell? (ex. SELECT fs_products_to_categories.categories_id, fs_products.products_id, fs_products_to_categories.products_id.....)

andrewsmd




msg:3808819
 8:12 pm on Dec 16, 2008 (gmt 0)

Do you need all of the fields. I think topr8 was saying you need to group by every field.

topr8




msg:3809079
 11:37 pm on Dec 16, 2008 (gmt 0)

OK, there are 2 things initially wrong with your query.

First, when you use the GROUP BY clause you need to list every field you select.
eg
SELECT price,title,location,time etc etc
GROUP BY price,title,location,time (in the order you want them grouped, usually it doesn't matter after the first item)
if you SELECT * then you must group by each the fields you have selected. (the * means all fields in the tables you have in the FROM clause: fs_products, fs_products_to_categories, fs_products_pricing

SECOND: you can only use GROUP BY when you are aggregating fields, eg COUNT , AVG, SUM etc

you are not aggregating any field in the select statement

topr8




msg:3809092
 11:51 pm on Dec 16, 2008 (gmt 0)

you need to do something like this i think:

SELECT MIN(pricing_price),productid
FROM fs_products, fs_products_to_categories, fs_products_pricing
WHERE
fs_products_to_categories.categories_id = 255
AND fs_products.products_id = fs_products_to_categories.products_id
AND fs_products_pricing.products_id = fs_products.products_id
AND fs_products.products_visibility = 1
GROUP BY productid

any other field you select as well you will then have to add to the group by list, as long as you list it after productid

wfernley




msg:3810032
 2:22 am on Dec 18, 2008 (gmt 0)

Thanks for all your suggestions!

After specifying the data I need, the command works!

Thanks again! :)

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