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

Question

I have the following SQL query which is searching for availability of users ensuring that they are not available between 2 selected dates:

SELECT users.username,
       users.user_id,
       users.first_name,
       users.last_name,
       users.description,
       projects_vs_users.project_id,
       sprints.sprint_start_date,
       sprints.sprint_end_date
FROM   sprints
       INNER JOIN projects
         ON sprints.project_id = projects.project_id
       INNER JOIN projects_vs_users
         ON projects.project_id = projects_vs_users.project_id
       RIGHT OUTER JOIN users
         ON projects_vs_users.user_id = users.user_id
WHERE  ( users.user_id NOT IN (SELECT users_1.username,
                                      users_1.user_id,
                                      users_1.first_name,
                                      users_1.last_name,
                                      users_1.description,
                                      projects_vs_users_1.project_id,
                                      sprints_1.sprint_start_date,
                                      sprints_1.sprint_end_date
                               FROM   sprints AS sprints_1
                                      INNER JOIN projects AS projects_1
                                        ON sprints_1.project_id = projects_1.project_id
                                      INNER JOIN projects_vs_users AS projects_vs_users_1
                                        ON projects_1.project_id = projects_vs_users_1.project_id
                                      RIGHT OUTER JOIN users AS users_1
                                        ON projects_vs_users_1.user_id = users_1.user_id
                               WHERE  ( sprints_1.sprint_start_date BETWEEN CONVERT(DATETIME, @startdate, 103) AND CONVERT(DATETIME, @enddate, 103) )
                                      AND ( sprints_1.sprint_end_date BETWEEN CONVERT(DATETIME, @startdate, 103) AND CONVERT(DATETIME, @enddate, 103) )) )

When I run the sub-query alone, it returns the values I require just fine. Therefore I assume there is some issue with the main query?

where users.userID not in (users_1.username,...) aren't comparing apples to apples... I think you just need only users.user_ID in the subselect. 1 field in the where clause compared to 8 fields in the subselect. should be 1 to 1. What is it you expect the system to do? randomly select the correct field to compare users.user_ID to? — xQbert
– xQbert, Commented Nov 24, 2015 at 20:20

user330315 · Accepted Answer · 2015-11-24 20:20:18Z

4

Your subquery

SELECT  users_1.username, users_1.user_id, users_1.first_name, ...

returns 8 columns - the expression on the left side of the IN condition is only one column (user_id).

And the error message is telling you that you can't compare one column to 8 columns. You need to change the sub-select to

SELECT users_1.user_id
FROM ...

answered Nov 24, 2015 at 20:20

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

mcclosa Over a year ago

That is perfect, thank you. I was trying to wrap my head around that for too long!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related