Solved

sql query help

Posted on 2014-11-13
7
123 Views
Last Modified: 2014-11-14
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
0
Comment
Question by:erikTsomik
7 Comments
 
LVL 11

Expert Comment

by:AkisC
ID: 40440642
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
0
 
LVL 11

Expert Comment

by:AkisC
ID: 40440644
if the above does not work
ALSO replace  COUNT(1) with  COUNT(*)
0
 
LVL 5

Accepted Solution

by:
TONY TAYLOR earned 500 total points
ID: 40440647
Try this...

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'
                HAVING COUNT(1) > 10
                  )  

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 11

Expert Comment

by:AkisC
ID: 40440649
A great tool for writing Sql is SqlPromt, but its not free

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

Expert Comment

by:Racim BOUDJAKDJI
ID: 40441104
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.
0
 
LVL 48

Expert Comment

by:Vitor Montalvão
ID: 40442069
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.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 40442110
@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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question