CODEWITHSUNDEEP
sql
mcclosa
mcclosa

Reputation: 1455

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

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?

Upvotes: 0

Views: 68

Answers (1)

user330315
user330315

Reputation:

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 ...

Upvotes: 4

Related Questions