Re: Help with an SQL query (opposite to a union query - if it exists)

pcb wrote: 
Hi MG,

Almost!! It works for the specific case of (A-01, A-02) on the sample
data, but not for the general case (i.e. A-01, A-02, A-03) or other
groups of IDs that are present in the larger data set. Thanks for you
help.


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Save this query:

SELECT C1.ID, C1.Code
FROM tblCodes AS C1 INNER JOIN tblCodes AS C2 ON C1.Code = C2.Code
WHERE C1.ID IN ('A-01','A-02','A-03')
GROUP BY C1.ID, C1.Code
HAVING Count(*) > 1;

as "Codes_matching" and then use the following query to get the Code
that is unique to all IDs.

SELECT C.Code
FROM (SELECT Code, COUNT(*) As CodeCount FROM Codes_matching GROUP BY
Code) As C
INNER JOIN (SELECT Count(*) As IDCount FROM (SELECT DISTINCT ID FROM
Codes_matching)) As I  ON C.CodeCount=I.IDCount

There may be a cleaner way to do this, but this is the 1st thing I could
get to work.  It would have been cleaner if Access had the
Count(Distinct ...) function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQxdhYIechKqOuFEgEQIttgCbB3V4B3ZYfHWhJzTBYHGbQhqjfZUAn2ms
Fp9vxDAfOHGxq+s2kTH2PGIa
=c7gO
-----END PGP SIGNATURE-----
.

Relevant Pages