CODEWITHSUNDEEP
sql-servert-sqlselect
leonyx
leonyx

Reputation: 857

SQL: Msg 116, Level 16, State 1, Line 1 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Please help me how to fixed this issue

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Here is my query

SELECT  a.RegId,
        a.PropertyThumbnail
FROM    tblPropertyDetail a
WHERE   a.RegId NOT IN 
        (
            SELECT  RegId,
                    COUNT(RegId) AS NumOccurrences
            FROM tblPropertyDetail
            GROUP BY RegId
            HAVING (COUNT(RegId) > 1)
        )

Upvotes: 0

Views: 27927

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

In SQLServer2005+ use CTE with aggregate window function

;WITH cte AS
 (
  SELECT RegId, PropertyThumbnail, COUNT(*) OVER (PARTITION BY RegId) AS cnt
  FROM tblPropertyDetail
  )
  SELECT RegId, PropertyThumbnail
  FROM cte
  WHERE cnt <= 1

Upvotes: 1

John Woo
John Woo

Reputation: 263843

remove this column in the subquery COUNT(RegId) AS NumOccurrences

SELECT a.RegId,
        a.PropertyThumbnail
FROM tblPropertyDetail a
WHERE a.RegId NOT IN (
                SELECT RegId
                FROM tblPropertyDetail
                GROUP BY RegId
                HAVING (COUNT(RegId) > 1)
                )

when using NOT IN, it is expected that the number of columns return by the subquery is only one.

Alternatively, you can also do this using JOIN

SELECT  a.RegId,
        a.PropertyThumbnail
FROM    tblPropertyDetail a
        LEFT JOIN
        (
            SELECT RegId, COUNT(RegId) AS NumOccurrences
            FROM tblPropertyDetail
            GROUP BY RegId
            HAVING (COUNT(RegId) > 1)
        ) b ON a.RegId = b.RegId
WHERE   b.RegId IS NULL

Upvotes: 6

Related Questions