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 - seewww.aspfaq.com/5006if you prefer a tested reply)
>
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis