CODEWITHSUNDEEP
mysqlsqlsql-server
Azri Jamil
Azri Jamil

Reputation: 2404

Equivalent SQL Query in MSSQL

Below query was successfully execute in MySQL but throwing an error in MSSQL. Can anybody provide me an example to equivalent query in MSSQL 2008?

SELECT DISTINCT TOP 20 
  [users].[id], [users].[email], [users].[first_name], [users].[last_name], 
  (SELECT groups_groups.name 
   FROM users AS groups_users 
     LEFT JOIN users_groups AS groups_users_groups ON groups_users_groups.user_id = groups_users.id 
     LEFT JOIN groups AS groups_groups ON groups_groups.id = groups_users_groups.group_id 
   WHERE users.id = groups_users_groups.user_id) AS [groups] 
FROM [users] GROUP BY [users].[id], [users].[email], [users].[first_name], [users].[last_name] 
ORDER BY [users].[id] DESC

Error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression

Upvotes: 0

Views: 456

Answers (4)

A_V
A_V

Reputation: 11

   SELECT users.id as id ,groups_groups.name  into #groups
   FROM users AS groups_users 
   LEFT JOIN users_groups AS groups_users_groups 
       ON groups_users_groups.user_id = groups_users.id 
   LEFT JOIN groups AS groups_groups 
        ON groups_groups.id = groups_users_groups.group_id 
   WHERE users.id = groups_users_groups.user_id

  SELECT DISTINCT TOP 20 
  [users].[id], [users].[email], [users].[first_name], [users].[last_name], g.name
  FROM [users] 
  inner join #groups g  on users.id=g.id 
  ORDER BY [users].[id] DESC

this is not a tested code snippet,though from query mentioned in ques it looks like,if aim was to get all the groups for that set of user data(duplicating the user data but new group names)and was throwing error as sub query for single field returned multiple values ....if above scenario is the case getting all the user.id and groups.name into temp table based on joining required tables and then doing the inner join to combine all the user data with the group names based on the id should get the required data

Upvotes: 0

Alberto Solano
Alberto Solano

Reputation: 8227

SQL Server returns that error because your subquery is returning multiple values and it cannot assign multiple values to a user in a single record.

I think you can do the following:

  1. Review the logic of your subquery, in order that it will return just only one record per user.
  2. Fix the data in order that your subquery will return only one record.

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

Incidentally, in MySQL, that subquery could be reduced to this...

SELECT DISTINCT g.name FROM groups_groups g

Upvotes: 0

CaldasGSM
CaldasGSM

Reputation: 3062

maybe your problem may be that the sub query is returning more than one result.. try

SELECT DISTINCT TOP 20 
  [users].[id], [users].[email], [users].[first_name], [users].[last_name], 
  (SELECT TOP 1 groups_groups.name 
   FROM users AS groups_users 
     LEFT JOIN users_groups AS groups_users_groups ON groups_users_groups.user_id = groups_users.id 
     LEFT JOIN groups AS groups_groups ON groups_groups.id = groups_users_groups.group_id 
   WHERE users.id = groups_users_groups.user_id) AS [groups] 
FROM [users] GROUP BY [users].[id], [users].[email], [users].[first_name], [users].[last_name] 
ORDER BY [users].[id] DESC

Upvotes: 0

Related Questions