"Where IN" with multiple columns (SQL Server)

Question

I'm Using SQL Server 2005.

The query would look like this

Select col1, col2, col3 from where (col1,col2) in

SQL Server doesn't seem to like that, any way of implementing that that anyone knows of that doesn't involve converting to varchars or anything else messy?

This is the actual query.

SELECT * 
FROM         
 (
    SELECT NEWID() AS guid, UserID, RoleId, ClubId, 0 AS GroupRole
    FROM dbo.Portal_UserRoles

    UNION

    SELECT NEWID() AS guid, dbo.Portal_UserGroups.UserId, 
           dbo.Portal_GroupRoles.RoleId, dbo.Portal_UserGroups.ClubId, 
           dbo.Portal_GroupRoles.GroupId AS GroupRole
    FROM dbo.Portal_GroupRoles 
    INNER JOIN dbo.Portal_UserGroups 
        ON dbo.Portal_GroupRoles.GroupId = dbo.Portal_UserGroups.GroupId
  ) AS derivedtbl_1
WHERE (derivedtbl_1.RoleId,derivedtbl_1.ClubId) IN 
      (
         SELECT Portal_GroupRoles.RoleId, Portal_ClubGroups.ClubId
         FROM Portal_GroupRoles 
         INNER JOIN Portal_ClubGroups
             ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId
      )

Answer 1

You have to separate in two clauses

 where col1 in (...) AND col2 in (...)

or you could refactor it a little bit

select * FROM (
     SELECT NEWID() AS guid, UserID, RoleId, ClubId, 0 AS GroupRole FROM dbo.Portal_UserRoles 
     UNION 
     SELECT NEWID() AS guid, dbo.Portal_UserGroups.UserId, dbo.Portal_GroupRoles.RoleId, dbo.Portal_UserGroups.ClubId, dbo.Portal_GroupRoles.GroupId AS GroupRoles FROM dbo.Portal_GroupRoles INNER JOIN dbo.Portal_UserGroups ON dbo.Portal_GroupRoles.GroupId = dbo.Portal_UserGroups.GroupId) 
     AS derivedtbl_1, Portal_GroupRoles, Portal_ClubGroup
where derivedtbl_1.RoleId = Portal_GroupRoles.RoleId
and derivedtbl_1.ClubId = Portal_ClubGroups.ClubId
and Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId

Answer 2

Do a join on the derived table instead of using the in

SELECT * 
FROM  
  (
    SELECT NEWID() AS guid, UserID, RoleId, ClubId, 0 AS GroupRole 
    FROM dbo.Portal_UserRoles 

    UNION 

    SELECT NEWID() AS guid, dbo.Portal_UserGroups.UserId, 
           dbo.Portal_GroupRoles.RoleId, dbo.Portal_UserGroups.ClubId,
           dbo.Portal_GroupRoles.GroupId AS GroupRole 
    FROM dbo.Portal_GroupRoles 
    INNER JOIN dbo.Portal_UserGroups 
        ON dbo.Portal_GroupRoles.GroupId = dbo.Portal_UserGroups.GroupId
  ) AS derivedtbl_1 

INNER JOIN 
  (
    SELECT Portal_GroupRoles.RoleId, Portal_ClubGroups.ClubId 
    FROM Portal_GroupRoles 
    INNER JOIN Portal_ClubGroups 
        ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId
  ) derivedtbl_2
    ON derivedtbl_1.RoleID = derivedtbl_2.RoleID 
      AND derivedtbl_1.ClubId = derivedtbl_2.ClubId

Answer 3

Nevermind, that seemed to work.

SELECT * 
FROM 
  (
    SELECT  NEWID() AS guid, UserID, RoleId, ClubId, 0 AS GroupRole
    FROM  dbo.Portal_UserRoles

    UNION

    SELECT  NEWID() AS guid, dbo.Portal_UserGroups.UserId,
            dbo.Portal_GroupRoles.RoleId, dbo.Portal_UserGroups.ClubId,
            dbo.Portal_GroupRoles.GroupId AS GroupRole
    FROM dbo.Portal_GroupRoles 
    INNER JOIN dbo.Portal_UserGroups 
        ON dbo.Portal_GroupRoles.GroupId = dbo.Portal_UserGroups.GroupId
  ) AS derivedtbl_1
WHERE derivedtbl_1.RoleId IN 
  (
    SELECT  Portal_GroupRoles.RoleId
    FROM Portal_GroupRoles 
    INNER JOIN Portal_ClubGroups 
        ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId
    WHERE derivedtbl_1.ClubId = Portal_ClubGroups.ClubId
  )

You can reference a table outside of a subquery :)

Answer 4

The standard, classic way to do what you seek is an EXISTS clause:

SELECT * 
    FROM         
    (
        SELECT NEWID() AS guid, UserID, RoleId, ClubId, 0 AS GroupRole
        FROM dbo.Portal_UserRoles

        UNION

        SELECT NEWID() AS guid, dbo.Portal_UserGroups.UserId, 
               dbo.Portal_GroupRoles.RoleId, dbo.Portal_UserGroups.ClubId, 
               dbo.Portal_GroupRoles.GroupId AS GroupRole
            FROM dbo.Portal_GroupRoles 
                 INNER JOIN dbo.Portal_UserGroups 
                 ON dbo.Portal_GroupRoles.GroupId = dbo.Portal_UserGroups.GroupId
    ) AS derivedtbl_1
WHERE EXISTS
  (
     SELECT Portal_GroupRoles.RoleId, Portal_ClubGroups.ClubId
         FROM (Portal_GroupRoles 
               INNER JOIN Portal_ClubGroups
               ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId) AS cgr
         WHERE derivedtbl_1.RoleID = cgr.RoleId
           AND derivedtbl_1.ClubId = cgr.ClubId
  )

Be wary of splitting the two-column condition into two separate IN clauses; it does not give you the same answer (in general) as the applying the two-column condition in one EXISTS clause.

Answer 5

SELECT 
 /*   
 your selected fields, joins here
 */
WHERE -- (derivedtbl_1.RoleId,derivedtbl_1.ClubId) IN 
 EXISTS
 (
  -- actually you can change these two fields to * (asterisk ) or 1, whatever, even your name, what matters only is the testing of existence(see below)
  SELECT Portal_GroupRoles.RoleId, Portal_ClubGroups.ClubId 
  FROM Portal_GroupRoles 
  INNER JOIN Portal_ClubGroups
  ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId

  -- here is your IN (the testing of existence):
  WHERE Portal_GroupRoles.RoleId = derivedtbl_1.RoleId AND 
  AND derivedtbl_1.ClubId = derivedtbl_1.ClubId
   )

alternatively:

SELECT 
 /*   
 your selected fields, joins here
 */
JOIN 
 (
  SELECT Portal_GroupRoles.RoleId, Portal_ClubGroups.ClubId
  FROM Portal_GroupRoles 
  INNER JOIN Portal_ClubGroups
  ON Portal_GroupRoles.GroupId = Portal_ClubGroups.GroupId
 ) X 
 -- here is your IN:
 ON X.RoleId = derivedtbl_1.RoleId
 AND X.ClubId = derivedtbl_1.ClubId