sql query help
I have the query where I Need to say id the sore not grater than 10 , but it through an error message
AND EXISTS (
SELECT COUNT(1)
FROM userScore US2 WITH (NOLOCK)
INNER JOIN lkup_VARoadSkill VA WITH (NOLOCK) ON US2.VARoadSkillKey = VA.VARoadSkillKey
WHERE U.userKey = US2.userKey
AND SU.sessionUnitKey = US2.sessionUnitKey
AND VA.VARoadSkillAddGradeIn = 1
AND US2.userScoreValue = '1'
) > 10
AND EXISTS (
SELECT COUNT(1)
FROM userScore US2 WITH (NOLOCK)
INNER JOIN lkup_VARoadSkill VA WITH (NOLOCK) ON US2.VARoadSkillKey = VA.VARoadSkillKey
WHERE U.userKey = US2.userKey
AND SU.sessionUnitKey = US2.sessionUnitKey
AND VA.VARoadSkillAddGradeIn = 1
AND US2.userScoreValue = '1'
) > 10
Last Comment
if the above does not work
ALSO replace COUNT(1) with COUNT(*)
ALSO replace COUNT(1) with COUNT(*)
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
A great tool for writing Sql is SqlPromt, but its not free
http://www.red-gate.com/products/sql-development/sql-prompt/
http://www.red-gate.com/products/sql-development/sql-prompt/
Please recheck the following JOIN conditions
U.userKey = US2.userKey
SU.sessionUnitKey = US2.sessionUnitKey
since neither U nor SU is specified as an alias anywhere in the subquery.
U.userKey = US2.userKey
SU.sessionUnitKey = US2.sessionUnitKey
since neither U nor SU is specified as an alias anywhere in the subquery.
If you are using the keyword EXISTS you can't compare the result with a value.
If you want to check for a collection of records that has more than 10 entries then TONY TAYLOR provided the correct solution, otherwise reformulate your question and post entire query.
If you want to check for a collection of records that has more than 10 entries then TONY TAYLOR provided the correct solution, otherwise reformulate your question and post entire query.
@erikTsomik,
I fully agree with Vitor.
It seems that you are not familiar enough with JOIN and EXISTS operators and syntax. Please reformulate the question because we do not have enough elements to help you.
Hope this helps.
I fully agree with Vitor.
It seems that you are not familiar enough with JOIN and EXISTS operators and syntax. Please reformulate the question because we do not have enough elements to help you.
Hope this helps.
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
AND (
SELECT COUNT(1)
FROM userScore US2 WITH (NOLOCK)
INNER JOIN lkup_VARoadSkill VA WITH (NOLOCK) ON US2.VARoadSkillKey = VA.VARoadSkillKey
WHERE U.userKey = US2.userKey
AND SU.sessionUnitKey = US2.sessionUnitKey
AND VA.VARoadSkillAddGradeIn = 1
AND US2.userScoreValue = '1'
) > 10