asked on

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

AkisC

Try deleting the word EXISTS

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

AkisC

if the above does not work
ALSO replace COUNT(1) with COUNT(*)

ASKER CERTIFIED SOLUTION

TONY TAYLOR

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AkisC

A great tool for writing Sql is SqlPromt, but its not free

http://www.red-gate.com/products/sql-development/sql-prompt/

Racim BOUDJAKDJI

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.

Vitor Montalvão

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.

Racim BOUDJAKDJI

@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.