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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-10 : 19:44:58
|
| Rich writes "My "product" records have a unique "ProdID" key. For reporting, the current SQL statment selects by column values in the "product" table and from joined "category" tables with one-to-many relationships. A product may exist in more than one category. The resulting record set then contains one product record for each category where it is found. Is there a procedure to remove the duplicate product records based upon the prodid for reporting purposes only even though one of the selection criteria is based upon looked-up categories?Here is a sample of existing SQL:SELECT Products.ProdID, Products.prodName, Sub_Categories.CatHierarchy FROM (Products INNER JOIN SubCatDetail ON Products.prodID = SubCatDetail.ProdID) INNER JOIN Sub_Categories ON SubCatdetail.subcatCategoryId = Sub_Categories.SubcatID WHERE SubCatDetail.subcatCategoryId IN (Select subcatID From Sub_Categories Where subcatCategoryId= SubCat) AND ... (more selects)Any suggestions appreciated. Thanks" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-10 : 20:39:51
|
| You can use SELECT DISTINCT instead of SELECT. However, if you include Category in the SELECT list DISTINCT won't remove duplicate ProdID's. |
 |
|
|
rfarrell
Starting Member
1 Post |
Posted - 2002-02-10 : 21:30:01
|
| Thanks for your response.Category is included in the select list. For example, I want to select on category "Clothes". Category Clothes has several sub-categories (Mens, Womens, Kids). A Product may exist within several of the category and sub-category levels. But, I only want to list the product once in the output. As you pointed out, SELECT DISTINCE doesn't work. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-10 : 21:33:19
|
| Then you need to GROUP BY ProdID and then use Min() or Max() for the category, if you don't want to list each category. SQL will return the ProdID each time; you'll have to have the report writer software suppress displaying the dupe IDs (MS Access does this in the report designer). |
|
|
|
|
|
|
Remove Duplicates in One-to-Many for Reporting