need help with inner join and oount sql server 2005
-
hi, I m trying to get values from three tables. 1) its has the sale id and product id 2) it has the product details based on id from 1st table, 3) it has the quantity of product being selected by the query. My problem is I need to check the count of the product based on various sizes and if its more than 0 i need to display available else sold out. I tried the following query. if I remove the count it works well to get the details but If I m gonna get the count it throws error. any suggestion where this query is wrong?? SELECT SaleProduct_tb.ProductId, Product_tb.BasePrice, Product_tb.SalePrice, Product_tb.PName, Product_tb.Image1, COUNT(ProductSizeQty.Quantity) AS Expr1 FROM SaleProduct_tb INNER JOIN Product_tb ON SaleProduct_tb.ProductId = Product_tb.ProductId INNER JOIN ProductSizeQty ON Product_tb.ProductId = ProductSizeQty.ProductId WHERE (SaleProduct_tb.SaleId = @SaleId) AND (SaleProduct_tb.CatId = @CatId) AND (SaleProduct_tb.SubCatId = @SubCatId) AND (Product_tb.BrandId = @BrandId) ORDER BY saleProduct_tb.ProductId
Vijay V. Yash Softech
-
hi, I m trying to get values from three tables. 1) its has the sale id and product id 2) it has the product details based on id from 1st table, 3) it has the quantity of product being selected by the query. My problem is I need to check the count of the product based on various sizes and if its more than 0 i need to display available else sold out. I tried the following query. if I remove the count it works well to get the details but If I m gonna get the count it throws error. any suggestion where this query is wrong?? SELECT SaleProduct_tb.ProductId, Product_tb.BasePrice, Product_tb.SalePrice, Product_tb.PName, Product_tb.Image1, COUNT(ProductSizeQty.Quantity) AS Expr1 FROM SaleProduct_tb INNER JOIN Product_tb ON SaleProduct_tb.ProductId = Product_tb.ProductId INNER JOIN ProductSizeQty ON Product_tb.ProductId = ProductSizeQty.ProductId WHERE (SaleProduct_tb.SaleId = @SaleId) AND (SaleProduct_tb.CatId = @CatId) AND (SaleProduct_tb.SubCatId = @SubCatId) AND (Product_tb.BrandId = @BrandId) ORDER BY saleProduct_tb.ProductId
Vijay V. Yash Softech
-
hi, I m trying to get values from three tables. 1) its has the sale id and product id 2) it has the product details based on id from 1st table, 3) it has the quantity of product being selected by the query. My problem is I need to check the count of the product based on various sizes and if its more than 0 i need to display available else sold out. I tried the following query. if I remove the count it works well to get the details but If I m gonna get the count it throws error. any suggestion where this query is wrong?? SELECT SaleProduct_tb.ProductId, Product_tb.BasePrice, Product_tb.SalePrice, Product_tb.PName, Product_tb.Image1, COUNT(ProductSizeQty.Quantity) AS Expr1 FROM SaleProduct_tb INNER JOIN Product_tb ON SaleProduct_tb.ProductId = Product_tb.ProductId INNER JOIN ProductSizeQty ON Product_tb.ProductId = ProductSizeQty.ProductId WHERE (SaleProduct_tb.SaleId = @SaleId) AND (SaleProduct_tb.CatId = @CatId) AND (SaleProduct_tb.SubCatId = @SubCatId) AND (Product_tb.BrandId = @BrandId) ORDER BY saleProduct_tb.ProductId
Vijay V. Yash Softech
add a GROUP BY clause (after the WHERE clause adn before the ORDER BY one) listing all the fields in the SELECT clause excpet the one you're COUNTing
-
to be fair the guy was told to post here after posting in the asp.net forum.
-
add a GROUP BY clause (after the WHERE clause adn before the ORDER BY one) listing all the fields in the SELECT clause excpet the one you're COUNTing
hi, thanks for the help.
Vijay V. Yash Softech
-
someone suggested me to put that here. So I added here too. sorry for that.
Vijay V. Yash Softech