DISTINCT with Many to Many relationship

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 2005 Forums

Transact-SQL (2005)

DISTINCT with Many to Many relationship

Author

Topic

emaynard
Starting Member

3 Posts

Posted - 2007-07-26 : 09:04:50

This has been kicking my butt for the last few days. I've tried just about every combination of joins that I can think of. Hopefully, one of the SQL gurus can help me out with this. Here's the scenario.

I have 3 tables tracking Products and Categories in a many-to-many relationship. Meaning a specific Product can belong to more than one Category.

Category
---------
[CategoryID] int IDENTITY(1, 1) NOT NULL,
[ParentID] int NULL,
[Title] nvarchar(255) NULL,
[Description] nvarchar(4000) NULL

Product
-------
[ProductID] int IDENTITY(1, 1) NOT NULL,
[Name] nvarchar(255) NULL,
[ShortDesc] nvarchar(4000) NULL

ProductCategory
---------------
[ProductCategoryID] int IDENTITY(1, 1) NOT NULL,
[ProductID] int NOT NULL,
[CategoryID] int NOT NULL

What I want to be able to do is to create a SELECT statement that will allow me to view all *DISTINCT* Products for a given parent category.

So for example, if I have a category for Apples and it contains child categories Red, Yellow, Large, Small and I then have several Products like Macintosh which I want to categorize this in both Red and Large. Others might be Red and Small and so on. Next I might have a parent Category called Pears and again several child categories which a given Product can be a member of multiple.

The problem that I'm running into is that when I simply want to see a listing of all Apples, I'm not having any luck as far as getting rid of the duplicate Product row produced by the fact that a given product can exist in different child Categories.

I hope this makes some sense. Here's what I've tried so far. Note: this includes a function I created to return the Parent CategoryID no matter where the product is listed in the hierarchy. Perhaps there is a better way to accomplish this, but I'm just getting my feet wet here with the advanced stuff.

SELECT MIN(Category.CategoryID), Product.Name, Product.ShortDesc, Product.ProductID, Category.Title, Category.ParentID
FROM Category INNER JOIN
ProductCategory ON Category.CategoryID = ProductCategory.CategoryID RIGHT OUTER JOIN
Product ON ProductCategory.ProductID = Product.ProductID
WHERE (Category.ParentID = (SELECT MIN([CategoryID]) AS CategoryID FROM fnGetBreadcrumbs(@CurrentCategory) WHERE ParentCat <> -1))
GROUP BY Product.Name, Product.Name, Product.ShortDesc, Product.ProductID, Category.Title, Category.ParentID

Thanks in advanced,
-eric

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-07-26 : 10:56:12

It would be helpful to see some sample data, but I think should help. You probably want to turn this into a proc.

Declare @CategoryID int
Set @CategoryID =

Select C.CategoryID, P.[Name], P.ShortDesc, P.ProductID, C.Title, C.ParentID
From Category C
Left Outer Join ProductCategory PC On C.CategoryID = PC.CategoryID
Left Outer Join Product P On PC.ProductID = P.ProductID
Where C.CategoryID In
(Select Distinct CategoryID
From Category
Where ParentID = @CategoryID
Or CategoryID = @CategoryID)

emaynard
Starting Member

3 Posts

Posted - 2007-07-26 : 11:07:54

JoeNak,

Thanks ever so much for your reply. I will try the proc suggestion.

In the meantime, what is the best way to present sample data?

In tabular form or scriptable format?

-eric

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-07-26 : 11:11:00

Either one is fine.

emaynard
Starting Member

3 Posts

Posted - 2007-07-26 : 11:33:53

Hmm.... First run of your suggested proc returns pretty much the same results as my original statement. Actually, it pulls in more records do to a few NULLS in ProductCategory (i think)

I will post some data later if I can't tweak the proc.

-eric

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2007-07-26 : 11:41:29

Yeah, it might, since I went with an outer join. You can always change back to an inner join.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 11:55:44

For faster response, please prove sample data and expected output.

E 12°55'05.76"
N 56°04'39.42"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources