"Où IN" avec plusieurs colonnes (SQL Server)

Je suis à l'aide de SQL Server 2005.

La requête devrait ressembler à ceci

Select col1, col2, col3 from <subquery> where (col1,col2) in <subquery>

SQL Server n'a pas l'air d'aimer cela. De toute façon de mise en oeuvre de ce que quelqu'un sait de qui n'implique pas de conversion à varchars ou autre chose malpropre?

C'est le réel de la requête.

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
      )

source d'informationauteur

  1. -3

    Vous devez séparer en deux clauses

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

    ou vous pourriez refactoriser un peu

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

    La norme, de la façon classique de faire ce que vous chercher est une clause EXISTS:

    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
  )

    Méfiez-vous de fractionner les deux colonnes condition en deux paragraphes; il ne vous donne pas la même réponse (en général) que l'application des deux-colonne état dans une clause EXISTS.

  3. 5

    Faire une jointure sur la table dérivée au lieu d'utiliser le 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
  4. 3
    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
  5. 2

    Le vôtre est utile de syntaxe et est pris en charge dans le standard SQL. Il n'est pas encore mis en œuvre dans SQL Server, mais vous pouvez voter en faveur de son inclusion ici.