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

What was the error?

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