Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2005
    Posts
    9

    Unanswered: MSAccess to Informix Translation

    Hi,

    Does anyone know how to translate the below query (access) into informix?

    SELECT X.USER_ID
    FROM
    (SELECT USERCERT.UserId as USER_ID, Count(1) as ITEM_COUNT FROM USERCERT GROUP BY USERCERT.UserId) AS X ,
    (SELECT MgCrlArlRevoke.CertOwnerId as USER_ID, Count(1) as ITEM_COUNT FROM MgCrlArlRevoke GROUP BY MgCrlArlRevoke.CertOwnerId) AS Y
    WHERE X.USER_ID = Y.USER_ID and Y.ITEM_COUNT =X.ITEM_COUNT;

    Thanks.

  2. #2
    Join Date
    Jan 2005
    Posts
    1

    RE:MSAccess to Informix Translation

    Question:

    Does anyone know how to translate the below query (access) into informix?

    SELECT X.USER_ID
    FROM
    (SELECT USERCERT.UserId as USER_ID, Count(1) as ITEM_COUNT FROM USERCERT GROUP BY USERCERT.UserId) AS X ,
    (SELECT MgCrlArlRevoke.CertOwnerId as USER_ID, Count(1) as ITEM_COUNT FROM MgCrlArlRevoke GROUP BY MgCrlArlRevoke.CertOwnerId) AS Y
    WHERE X.USER_ID = Y.USER_ID and Y.ITEM_COUNT =X.ITEM_COUNT;

    Please try the following statement in Informix:

    SELECT X.UserId
    FROM USERCERT X
    GROUP BY X.UserId
    HAVING COUNT(*) = (SELECT COUNT(*) FROM MgCrlArlRevoke Y WHERE Y.CertOwnerId = X.UserId )

    I not really tested this statement and hope it work fine. Please let me know whether this statement work fine in your environment.

  3. #3
    Join Date
    Jan 2005
    Posts
    9
    Thank you very much, worked great!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •