How do I get rid of this error? Sql server

Question

0.00/5 (No votes)

See more:

This is an SQL query that I am trying to execute, Reviewer.rEmail and PAPER_REVIEW.reviewerId have the same entries and I want to make a separate column of those entires, but I keep getting the error below:

SQL

select REVIEWER.rEmail from REVIEWER where REVIEWER.rEmail in (select PAPER_REVIEW.reviewerId, AVG(PAPER_REVIEW.score) AS AVG_SCORE from PAPER_REVIEW group by PAPER_REVIEW.reviewerId
having AVG(PAPER_REVIEW.score) >= 6);

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

What I have tried:

I don't know how to fix it, I have tried using with but it gave me a syntax error. Someone please help.

Posted 6-Apr-20 18:11pm

PriyavartRajain

Updated 6-Apr-20 19:53pm

PIEBALDconsult

v3

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Accepted Answer · 2020-04-06T18:48:00

Your query itself doesn't make sense so I'm not quite sure what you are attempting to do. Given we don't have access to your code, computer, nor do we know anything about your project i'll try and guide you in a general direction.

First, your query is using a where clause where you are trying to get all values of rEmail that appear in a subquery. You've got a few issues here.

A subquery can only return 1 value when combined with "IN" and you are returning 2.

Your second issue is that you are looking for WHERE rEmail IN but your 2 columns you are attemping to search in are an Id column and an aggregate that have nothing to do with an email address.

With that said here is a general idea with example schema of what you need to do.

SQL

DECLARE @Reviewer TABLE (
   rEmail varchar(200) NULL
);

DECLARE @PaperReviewer TABLE (
   rEmail varchar(200) NULL
);

INSERT @Reviewer (rEmail) VALUES ('Test1');
INSERT @Reviewer (rEmail) VALUES ('Test2');
INSERT @PaperReviewer (rEmail) VALUES ('Test2');

SELECT * FROM @Reviewer WHERE rEmail IN (SELECT rEmail FROM @PaperReviewer);

The above example would work (providing i didn't fat finger anything, didn't run it myself) in that you should only see Test2 returned in that query.

Your alternative is to use a WHERE / EXISTS clause if you need to look up the record by multiple columns.

Given you used an IN clause i won't go down that path as i think your issue is you probably are either new to sql or don't understand your schema.

If you have questions i'll do my best to help but with the little info i have, the above should provide what you need.

PIEBALDconsult · Accepted Answer · 2020-04-06T19:30:00

How about something like this:

SQL

DROP TABLE #REVIEWER
;
SELECT *
INTO #REVIEWER
FROM
(
  SELECT 1 ID , 'email1@foo.bar' Email
  UNION ALL
  SELECT 2 ID , 'email2@foo.bar' Email
) T
;
SELECT * FROM #REVIEWER
;
DROP TABLE #REVIEW
;
SELECT *
INTO #REVIEW
FROM
(
  SELECT 1 ID , 4.2 Score
  UNION ALL
  SELECT 2 ID , 6.9 Score
) T
;
SELECT * FROM #REVIEW
;


WITH review AS
(
  SELECT ID
  FROM
  (
    SELECT ID
    , AVG ( Score ) AvgScore
    FROM #REVIEW
    GROUP BY ID
  ) T
  WHERE AvgScore >= 6
)
SELECT A.*
FROM #REVIEWER A
JOIN review B
ON A.ID = B.ID

Maciej Los · Accepted Answer · 2020-04-06T19:53:00

When you use IN clause, you have to specify the field in subquery which corresponds to the left side of expression.
So, this query:

SQL

where REVIEWER.rEmail in (select PAPER_REVIEW.reviewerId, AVG(PAPER_REVIEW.score)

is trying to compare rEmail to incomparable fields reviewerId and AVG(PAPER_REVIEW.score).

For further details, please see:
See:
IN (Transact-SQL) - SQL Server | Microsoft Docs[^]
EXISTS (Transact-SQL) - SQL Server | Microsoft Docs[^]

Seems, you want to get average of score from PAPER_REVIEW. So, you need to use JOIN[^].

SQL

SELECT rv.rEmail, AVG(pr.score) AS AVG_SCORE
FROM REVIEWER rv INNER JOIN PAPER_REVIEW pr ON rv.reviewerId = pr.reviewerId
GROUP BY rv.rEmail
HAVING AVG(pr.score) >= 6;

How do I get rid of this error? Sql server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0