Solved

sql query help

Posted on 2014-11-13
7
119 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 45

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now