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

Your name:

Reply:


Posted by nick on 09/14/07 21:39

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

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация