-
Newbie question - Trouble grouping data
Hi all,
I'm having a bit of a problem grouping data together. In a nutshell, i'm trying to extract information from two tables; a Products table and Merchant_Products table.
The Products table contains a single product and the Merchant_Product contains all linked products. I'm trying to pull out the lowest priced product from Merchant_Products but when i'm using the Group By command i get an error. I'm using the following query to extract the information:
SELECT Merchant_Products.Merchant_Product_Price, Products.Product_Name, Products.Product_Description, Products.Product_Image_URL,
Products.Product_ID, Category.Category_ID
FROM Merchant_Products INNER JOIN
Products ON Merchant_Products.Product_ID = Products.Product_ID INNER JOIN
Category ON Products.Cat_ID = Category.Category_ID
ORDER BY Products.Product_ID
Any help would be much appreciated as i'm truly stuck.
Cheers,
Sach
-
-
Hi rmiao,
I'm not getting the following error message:
Msg 8120, Level 16, State 1, Line 1
Column 'Merchant_Products.Merchant_Product_Price' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Here's the full query:
SELECT Merchant_Products.Merchant_Product_Price, Products.Product_Name, Products.Product_Description, Products.Product_Image_URL,
Products.Product_ID, Category.Category_ID
FROM Merchant_Products INNER JOIN
Products ON Merchant_Products.Product_ID = Products.Product_ID INNER JOIN
Category ON Products.Cat_ID = Category.Category_ID
group BY Products.Product_ID
If i add the column from the error message in the query then i get another error message which asks me for another column. In a nutshell, the error messages runs through all the table columns which i've defined. If i add all of them then the all the data comes through, which isn't what i want.
Thanks in advance, your help is much appreciated.
Sach
-
The message tells all, every column in select list should be used in aggregate function or GROUP BY clause.
-
Hi rmiao,
If i do what you're suggesting then i get all the results back which isn't what i want. I only want the lowest priced item from the Merchant_Products table which relates to the Product_ID in the Products table.
Thanks again for your help.
Sach
-
It's not my suggestion, but the way sql works.
-
I think you want to do something like this:
SELECT
MinPrices.Product_ID,
MinPrices.Merchant_Product_Price,
Products.Product_Name,
Products.Product_Description,
Products.Product_Image_URL,
Category.Category_ID
FROM
(SELECT
Products.Product_ID,
MIN(Merchant_Products.Merchant_Product_Price) AS Merchant_Product_Price
FROM Merchant_Products
JOIN Products
ON Merchant_Products.Product_ID = Products.Product_ID
JOIN Category
ON Products.Cat_ID = Category.Category_ID
GROUP BY Products.Product_ID) AS MinPrices
JOIN Products
ON MinPrices.Product_ID = Products.Product_ID
JOIN Category
ON Products.Cat_ID = Category.Category_ID
-
Hi nosepicker,
Thanks for the suggestions, i'll test it tonight and let you know how i get on.
Can anyone suggest any sites that i could use to improve my knowledge on writing SQL query scripts?
Thanks
Sachin
-
Hi nosepicker,
I've had a quick look and it seems to work! Thanks for your help; it really has helped me out!
Sachin
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|