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.

Re: Reducing many-to-many to one-to-one

On Fri, 14 Sep 2007 14:43:33 -0000, nick@nova5.net wrote:

>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

Re: Reducing many-to-many to one-to-one

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.

> 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