Welcome to WebmasterWorld Guest from 54.147.250.33

Forum Moderators: open

Complex MySQL Order BY Group

Can anyone help? :)

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

WebmasterWorld Senior Member 10+ Year Member



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

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

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.

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

WebmasterWorld Senior Member 10+ Year Member



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.....)

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

WebmasterWorld Senior Member 5+ Year Member



Do you need all of the fields. I think topr8 was saying you need to group by every field.
11:37 pm on Dec 16, 2008 (gmt 0)

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

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

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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

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

WebmasterWorld Senior Member 10+ Year Member



Thanks for all your suggestions!

After specifying the data I need, the command works!

Thanks again! :)

 

Featured Threads

Hot Threads This Week

Hot Threads This Month