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.

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.

Thank you very much, worked great!