SELECT INTO inside a subquery

Question

Environment : SQL Server 2005

I have this type of request

SELECT *
FROM user
WHERE EXISTS(
  SELECT *
  FROM user_access
  WHERE user_access.user_id = user.user_id
  AND user_access.access IN ('HIGH_LEVEL','MEDIUM_LEVEL')
)
AND EXISTS(
SELECT *
FROM user_service
WHERE user_service.role ='Manager'
AND user.id_user = user_service.user_id
)
AND user.is_active ='True'

But in the result I'd also like to get the result from the subquery, so I though about something like

DECLARE @user_access TABLE(user_id int,access nvarchar(30))
DECLARE @user_service TABLE(user_id int,service_id int,role nvarchar(10))
SELECT *  FROM user
    WHERE EXISTS(
      SELECT user_id,access INTO  [@user_access]
      FROM user_access
      WHERE user_access.user_id = user.user_id
      AND user_access.access IN ('HIGH_LEVEL','MEDIUM_LEVEL')
    )
AND EXISTS(
SELECT user_id , service_id,role INTO @user_service
FROM user_service
WHERE user_service.role ='Manager'
AND user.id_user = user_service.user_id
)
    AND user.is_active ='True'


SELECT * FROM @user_acess
select * from @user_service

But I get the following error :

"Incorrect syntax near the keyword 'INTO'."

Do you have any idea how I can do it without doing twice the subquery (I tried with a temp table, same error) ?

EDIT : I'm sorry for my fellow who tried to solve my problem, I forget one thing : I have 2 subquery. See the requests. I want :

• All the active user, with high or medium access and who are manager
• All the service in which these users are
• All the access of these users

Answer 1

A temp table or variable would appear to be the simplest solution. Stuff the results of the subquery into a temp table or variable and then execute two select statements at the end of your procedure.

Declare @UserAccesses Table (
                            user_id ...
                            , access varchar(...)
                            )

Insert @UserAccesses( user_id, access )
Select UA.user_id, UA.access
From user_access As UA
    Join user As U
        On U.user_id = UA.user_Id
Where U.is_active = 'True'
    And UA.access In('HIGH_LEVEL', 'MEDIUM_LEVEL')

Select ...
From user
Where Exists    (
                Select 1
                From @UserAccess As UA1
                Where UA1.user_id = user.user_id
                )

Select user_id, access
From @UserAccesses

Update given your expansion of the original question

The solution for two subqueries is essentially the same as with one except that instead of returning two resultsets, you return three. As before, you use a temp table/variable per subquery:

Declare @UserAccesses Table (
                            user_id int
                            , access nvarchar(30)
                            )

Declare @UserServices Table (
                            user_id int
                            , service_id int
                            , role nvarchar(10)
                            )

Insert @UserAccesses( user_id, access )
Select UA.user_id, UA.access
From user_access As UA
    Join user As U
        On U.user_id = UA.user_Id
Where U.is_active = 'True'
    And UA.access In('HIGH_LEVEL', 'MEDIUM_LEVEL')

Insert @UserServices( user_id, service_id, role )
Select user_id , service_id,role
From user_service
Where user_service.role ='Manager'
    And Exists  (
                Select 1
                From @UserAccesses As UA1
                Where UA1.user_id = user_service.user_id
                )

Select ...
From user
Where Exists    (
                Select 1
                From @UserServices As US1
                Where US1.user_id = user.user_id
                )

Select user_id, access
From @UserAccesses As UA
Where Exists    (
                    Select 1
                    From @UserServices As US1
                    Where US1.user_id = UA.user_id
                    )

Select user_id, access
From @UserServices

Answer 2

Here's an implementation of what Ken Down^ has suggested and I think its result should not contain any extraneous rows, considering what you are after.

DECLARE @user_access TABLE(user_id int,access nvarchar(30))
DECLARE @user_service TABLE(user_id int,service_id int,role nvarchar(10))

INSERT INTO @user_access
SELECT ua.user_id, ua.access
FROM user_access ua
  INNER JOIN [user] u ON ua.user_id = u.user_id
WHERE u.is_active ='True'
  AND ua.access IN ('HIGH_LEVEL','MEDIUM_LEVEL')

INSERT INTO @user_service
SELECT us.user_id , us.service_id, us.role
FROM user_service us
  INNER JOIN [user] u ON us.user_id = u.user_id
WHERE u.is_active ='True'
  AND us.role ='Manager'


SELECT u.*
FROM [user] u
  INNER JOIN (
    SELECT user_id FROM @user_access
    UNION
    SELECT user_id FROM @user_service
  ) uas ON uas.user_id = u.user_id
SELECT * FROM @user_acess
SELECT * FROM @user_service

Answer 3

This is about the best you can do to optimize. There is no way to capture and retain the data in the EXISTS subqueries - especially when EXISTS does not fully evaluate the result set. It short circuits when a SINGLE match is found in the subquery (one access for the user) so you can't get all the access records from it anyway.

declare @user table (user_id int)
insert @user
SELECT [user].user_id
FROM [user]
WHERE EXISTS(
    SELECT *
    FROM user_access
    WHERE user_access.user_id = [user].user_id
    AND user_access.access IN ('HIGH_LEVEL','MEDIUM_LEVEL')
    )
AND EXISTS(
    SELECT *
    FROM user_service
    WHERE user_service.role ='Manager'
    AND [user].id_user = user_service.user_id
    )
AND [user].is_active ='True'

SELECT [user].* FROM [user] inner join @user u on u.USER_ID = [user].user_id
SELECT a.user_id, a.access FROM user_access a inner join @user u on u.USER_ID = a.user_id
SELECT s.user_id, s.service_id, s.role FROM user_service s inner join @user u on u.USER_ID = s.user_id

Answer 4

You can pull the subquery out to make the temp table, then do the two queries you want out of the temp table.

Answer 5

This should return the result set from both tables.

SELECT *
FROM user u, user_access a
WHERE u.user_id in (
  SELECT user_access.user_id
  FROM user_access
  WHERE user_access.user_id = u.user_id
  AND user_access.access IN ('HIGH_LEVEL','MEDIUM_LEVEL')
  AND user_access.user_id = u.user_id
)
AND a.user_id = u.user_id
AND u.is_active ='True'