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.
| Author |
Topic |
|
jgephart
Starting Member
2 Posts |
 Posted - 2004-09-09 : 22:13:44
|
| This is a question I've had a million times, but I never found an answer I liked. I want to create a view that returns products from my database, along with the category that each product belongs to. Products can belong to several categories, but I just want to select the top 1 for each. Is there any way to accomplish this without using a subquery? Here's what I usually end up doing, but it gives me permission headaches that I'd rather avoid:select ProductID, ProductName, (select top 1 CategoryName from ProductCategory where ProductID=ProductID) as CategoryNamefrom Product |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-09 : 23:00:37
|
| I guess you could do something like this instead:select P.ProductID,P.ProductName,MAX(CategoryName)from Product as PJOIN ProductCategory as PCON PC.ProductID=P.ProductIDGROUP BY P.ProductID, P.ProductNamebut I don't know if its any "better"!Kristen |
 |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2004-09-09 : 23:34:53
|
| permission headaches? What do you mean?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2004-09-10 : 03:44:58
|
| Do Kristen's GROUP BY,that way you can select MIN or MAX depending on how you want to "sort" alphabetically.With your technique you could theoretically receive different results for each run of the select.rockmoose/* Chaos is the nature of things...Order is a lesser state of chaos */ |
|
|
|
jgephart
Starting Member
2 Posts |
Posted - 2004-09-10 : 17:12:41
|
| Thanks all, for the suggestions. My actual query returns a lot more fields related to product, so I'd have to group on all of those in order to try that tactic, but I might just give it a shot and see how it works out.The permissions headache, which of course I can't get to happen now that I'm trying to come up with a solid example, involves that old enemy, dynamic sql. If I recall past situations correctly, it seems I've run into trouble when building a dynamic select statement inside a stored procedure. Something about the dynamic sql in a sproc trying to select from a view which selects directly from a table via subquery... eh, I can't remember exactly, but somehow I've managed situations where it requires me to give select permissions on the subquery table, and I don't like assigning permissions like that. But, as I said, I'm not having a problem this time, so clearly I'll forget about it again until the next time it comes up :-)Thanks! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-09-10 : 17:15:43
|
| When you use dynamic sql, the user or role needs direct table level access. Granting EXEC on stored procedures is not enough. This is one of the reasons why dynamic sql should be avoided if possible. It has to do with the dynamic sql not running in the same context of the stored procedure.Tara |
|
|
|
|
|
|
|
|
Join Top 1