help with query!

I have 2 tables

tblStyles
StyleID int (pk)
XMLfilename nvarchar(50)

tblRules
RuleID int (pk)
Usercode int
StyleID int (fk)
ruleindex tinyint

Now in a stored proc I want to retreive XMLfilename for the first rule that matches certain criteria.
So if Usercode 5 would have 3 rules defined with ruleindex: 8, 6 and 7 (they might be in that order in the table)
and both rule 6 and 8 would match my criteria, I would like to have the xmlfilename related to the rulerecord with ruleindex 6 (as 6 is the lowest ruleindex).

Here's what I have so far:
----------
declare @.style nvarchar(50)

set @.style=(

select XMLfileid FROM (

select us.XMLfileid,r.ruleindex from tblRules r
inner join tblUserStyles us on us.StyleID=r.StyleID
wherer.usercode=@.Usercode
order by ruleindex

) as tmp

WHERE <allmycriteria>

)
----------

I get this error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

declare @.style nvarchar(50)

set @.style=(

select XMLfileid FROM (

select Top 1 us.XMLfileid,r.ruleindex from tblRules r
inner join tblUserStyles us on us.StyleID=r.StyleID
wherer.usercode=@.Usercode
order by ruleindex asc

) as tmp

WHERE <allmycriteria>

)

Coz u need only one file name of the samlest index matched . You cannot use Order By in a Sub Query without using Top

alternative to your query you can use

1Create Procedure GetXmlFile2(3@.UserCodevarchar(10)4)5AS67BEGIN89declare @.stylenvarchar(50)1011set @.style=(1213select XMLfileidFROM tblStyleswhere StyleIdin(14Select Top 1 TR.StyleIdFROM15tblRulesINNERJOIN tblStyle TSon TS.StyleId=TR.StyleId16Where TR.usercode=@.UserCode17order by Tr.ruleindexasc18)1920WHERE21Select @.Style22END23

|||

It seems to me that having the scalar value assigned in the stored procedure is overkill. Review this and let me know if it works for you:

Create Procedure GetXmlFile( @.UserCodevarchar(10))ASBEGIN SELECT ts.XMLfileidFROM tblStyles tsWHERE ts.StyleId = (select min(tr.StyleId)from tblRules trwhere tr.usercode = @.UserCodegroup by tr.usercode )END

help with query

I have a table UserData which amongst others contains a field UserCode (int) and Name and Country.

I also have a table UsersAndSports which contains a field UserCode and a field SportID (both int).
This table can have multiple rows for the same usercode:
UserCode SportID
12 7
12 9
12 4
15 9
15 8

I want a user on my site to search for users with an interest for the same sports (say SportID 7 OR 9).
In this case that would return UserCode 12 two times and UserCode 15 once. I just need the matching UserCode once.

I want to have a query that returns the UserCode, Name, Country AND the SportID.
What would that query be?

I tried something like this (which doesnt work ofcourse :) ):

SELECT tblUserData.UserName,UserCode,Country FROM aspnet_Users
INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID
INNER JOIN tblUserData ON tblUserData.UserCode IN (SELECT DISTINCT UserCode FROM tblUsersAndSports WHERE SportID=7 OR SportID=9)
WHERE tblUserData.Username<>''

I don't really understand. You say:

I want to have a query that returns the UserCode, Name, Country AND the SportID

But you also only want to return one user? But if a user has more than one SportID than which SportID should it be returning?|||Excuse me..I just want to return a user which has sportid x or y. I dont need to retreive the sportid's.

So when a user searches for other users who have sportid 7 OR 9, and user 1843 has sportID 7 and 9, then just usercode 1843 is returned.

I hope this clarifies my problem?|||OK, I think I understand now. In that case you can just do this:

SELECT DISTINCT UserData.UserCode
FROM UserData INNER JOIN
UsersAndSports ON UserData.UserCode = UsersAndSports.UserCode
WHERE (SportID = 7 OR SportID = 9)|||now I see it it's actually quite easy...:)
Thanks!