Forum Moderators: open
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
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.
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.....)
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
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
After specifying the data I need, the command works!
Thanks again! :)
