Reducing many-to-many to one-to-one

Hi,

I have the following three tables below containing Resources,
Categories and a link table so each Resource can belong to one or more
Categories. I would like to create a view (ResourceID, ResourceName,
CategoryID, CategoryName) that includes one row for each Resource with
just one of the Categories that it belongs to.

Resource table
- ResourceID
- ResourceName
- etc..

Category table
- CategoryID
- CategoryName
- etc..

ResourceCategory table
- ResourceID
- CategoryID

Can anyone help? Thanks.On Fri, 14 Sep 2007 14:43:33 -0000, nick@.nova5.net wrote:

Quote:

Originally Posted by

>Hi,
>
>I have the following three tables below containing Resources,
>Categories and a link table so each Resource can belong to one or more
>Categories. I would like to create a view (ResourceID, ResourceName,
>CategoryID, CategoryName) that includes one row for each Resource with
>just one of the Categories that it belongs to.

Hi Nick,

That's only possible if you somehow specify WHICH of the categories you
want. Lowest CategoryID? Longest CategoryName? CategoryID closest to a
multiple of 42?

I'm sure that you don't care, but since SQL Server has no "just give me
any I don't care which" operator, you'll have to specify something.

Assuming you want the lowest CategoryID, you can use the following on
SQL Server 7.0 and up (on SQL 2005, you might find a better solution
with CTEs and CROSS APPLY, but since you failed to specify the version,
I'll play it safe):

CREATE VIEW YourShinyNewView
AS
SELECT r.ResourceID, r.ResourceName,
c.CategoryID, c.CategoryName
FROM Resource AS r
INNER JOIN (SELECT Resource, MIN(CategoryID) AS MinCategoryID
FROM ResourceCategory
GROUP BY Resource) AS rc
ON rc.ResourceID = r.ResourceID
INNER JOIN Category AS c
ON c.CategoryID = rc.MinCategoryID;

(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Hi Hugo,

Thanks for help, just what I needed. I was trying to put a 'top 1' in
the nested select which obviously (now!) didn't work..

Nick.

Quote:

Originally Posted by

Hi Nick,
>
That's only possible if you somehow specify WHICH of the categories you
want. Lowest CategoryID? Longest CategoryName? CategoryID closest to a
multiple of 42?
>
I'm sure that you don't care, but since SQL Server has no "just give me
any I don't care which" operator, you'll have to specify something.
>
Assuming you want the lowest CategoryID, you can use the following on
SQL Server 7.0 and up (on SQL 2005, you might find a better solution
with CTEs and CROSS APPLY, but since you failed to specify the version,
I'll play it safe):
>
CREATE VIEW YourShinyNewView
AS
SELECT r.ResourceID, r.ResourceName,
c.CategoryID, c.CategoryName
FROM Resource AS r
INNER JOIN (SELECT Resource, MIN(CategoryID) AS MinCategoryID
FROM ResourceCategory
GROUP BY Resource) AS rc
ON rc.ResourceID = r.ResourceID
INNER JOIN Category AS c
ON c.CategoryID = rc.MinCategoryID;
>
(Untested - seewww.aspfaq.com/5006if you prefer a tested reply)
>
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis