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 |
jamie_pattison
Yak Posting Veteran
65 Posts |
Posted - 2013-03-25 : 06:22:51
|
I have some SQL code which retrieves a row (if exists) from a table. The code isDeclare @Max moneyDeclare @Min moneydeclare @UserID intdeclare @ProfileID intdeclare @SubjectID intset @Min = 0set @Max = 500set @UserID = 29set @ProfileID = 1set @SubjectID = 1SELECT *FROM Rate INNER JOIN UserRate ON Rate.ID = UserRate.RateIDWHERE (UserID =@UserID)AND(ProfileID = @ProfileID) AND(SubjectID = @SubjectID) AND(@Min >= MinAmount and @Min <= MaxAmount) OR (@Max >= MinAmount and @Max <= MaxAmount) OR(@Min <= MinAmount and @Max > MinAmount)What im trying to get is to see if a rate exist for a User based on their profile, subject and the rates.You can have multiple rates but not for the same min and max amount of if the rate falls into this range.When i pass in the values (min,max, userid, subjectid and profileid) i get a row back for the userID which does NOT belong to the UserID i passed in but has that rate availableSo as an example, if i pass in User 1 i get a row back with his details along with the min and max amounts.If i pass in user 2 i get back User 1 results but the userID shown is for USER 1 - So i think my WHERE clause is a little wrong somewhere?Could anyone advise?Thanks |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-03-25 : 08:38:57
|
Can you exlain your problem with some sample data?Take some input data for Rate and UserRate tables and then explain your problem....--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-25 : 09:46:45
|
make where clause like below and see[code]..WHERE (UserID =@UserID)AND(ProfileID = @ProfileID) AND(SubjectID = @SubjectID) AND((@Min >= MinAmount and @Min <= MaxAmount) OR (@Max >= MinAmount and @Max <= MaxAmount) OR(@Min <= MinAmount and @Max > MinAmount))------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-03-25 : 14:29:48
|
Or:WHERE (UserID = @UserID) AND(ProfileID = @ProfileID) AND(SubjectID = @SubjectID) ANDNOT (@Min > MaxAmount OR @Max < MinAmount) |
|
|
|
|
|