SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-12-17 : 08:13:35

Matt writes "G'day,

Im using this query:

SELECT Products.ProductID, ProductDetails.ProductDetailID,
Products.ProductName, ProductDetails.ProductDetail1,
ProductDetails.ProductDetail2, ProductDetails.UnitPrice,
Products.CategoryID
FROM Products INNER JOIN ProductDetails ON Products.ProductID =
ProductDetails.ProductID

Currently, it displays all records (from the Products table) twice as
each record has two unique options from the ProductDetails table.

How can I make it group to display the product name once, but both its
details from the ProductDetails table.

Using GROUP BY ProductDetails.ProductName gives me an error

Thanks in advance.

Matt"

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-12-17 : 08:16:54

That's because when you use a GROUP BY, you have to have all fields being selected within the GROUP BY or within an aggregate function. I would suggest using a derived table instead of the table Products, something like the following:

SELECT Products.ProductID, ProductDetails.ProductDetailID,
Products.ProductName, ProductDetails.ProductDetail1,
ProductDetails.ProductDetail2, ProductDetails.UnitPrice,
Products.CategoryID
FROM (SELECT Products.ProductID FROM tblProducts GROUP BY ProductID) t1 INNER JOIN ProductDetails ON t1.ProductID =
ProductDetails.ProductID

Just make sure to include all the appropriate fields in the derived table, and then eiter include the extra fields in the GROUP BY or an aggregate function.

***************************************
Death must absolutely come to enemies of the code!

Nazim
A custom title

1408 Posts

Posted - 2002-12-17 : 08:22:13

hmmm if i have understood the question properly. urs looks basically a report formatting kidna problem.for it you are better off using a reporting tool like Crystal reports.

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources