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

[bradleyp]

Hi all,

I have a table (tblCodes) that has two fields: 'ID' and 'Code'.
A small portion of the table is as follows:

|-------------|
| tblCodes |
|-------------|
| ID | Code |
|------|------|
| A-01 | TBN |
| A-01 | SPN |
| A-01 | EWK |
| A-01 | EMX |
| A-02 | QIM |
| A-02 | TBN |
| A-02 | EWK |
| A-03 | TBN |
| A-04 | DOY |
| A-04 | CAV |
| A-04 | ICP |
| A-04 | CYG |
| A-04 | MER |
| A-05 | ICP |
|------|------|

The table can grow very large. ID numbers in the
thousands each with up to 400 different codes (but
usually a max of 20 codes per ID, with an average
of between 2-5 codes per ID).

Is there a way to build a SQL statement that returns
the codes that intersect 2 or more IDs? For example,

'A-01' Intersects 'A-02' = TBN, EWK

or

'A-01' Intersects 'A-02' Intersects 'A-03' = TBN

I've been trying to figure a way for a while now with
no luck. I'm at a point were I might just loop throgh
the table and pass duplicates to an array, but I suspect
it will be very slow on large tables. Any ideas or pointers
would be very helpful. Thank you.

Pete

[MGFoster]

br******@inspection.gc.ca wrote:

Hi all,

I have a table (tblCodes) that has two fields: 'ID' and 'Code'.
A small portion of the table is as follows:

|-------------|
| tblCodes |
|-------------|
| ID | Code |
|------|------|
| A-01 | TBN |
| A-01 | SPN |
| A-01 | EWK |
| A-01 | EMX |
| A-02 | QIM |
| A-02 | TBN |
| A-02 | EWK |
| A-03 | TBN |
| A-04 | DOY |
| A-04 | CAV |
| A-04 | ICP |
| A-04 | CYG |
| A-04 | MER |
| A-05 | ICP |
|------|------|

The table can grow very large. ID numbers in the
thousands each with up to 400 different codes (but
usually a max of 20 codes per ID, with an average
of between 2-5 codes per ID).

Is there a way to build a SQL statement that returns
the codes that intersect 2 or more IDs? For example,

'A-01' Intersects 'A-02' = TBN, EWK

or

'A-01' Intersects 'A-02' Intersects 'A-03' = TBN

I've been trying to figure a way for a while now with
no luck. I'm at a point were I might just loop throgh
the table and pass duplicates to an array, but I suspect
it will be very slow on large tables. Any ideas or pointers
would be very helpful. Thank you.

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

Will this do:

SELECT C1.ID, C1.Code
FROM tblCodes As C1 INNER JOIN tblCodes As C2
ON C1.Code = C2.Code
GROUP BY C1.ID, C1.Code
HAVING Count(*) > 1
ORDER BY C1.CODE, C1.ID

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQxYOSoechKqOuFEgEQL4SgCgi42es58lXIVTDf7SOz93Er zv8e8An0tm
KUVa7becMpFx8ZFKK82DzkVY
=s77s
-----END PGP SIGNATURE-----

[pcb]

Hi MG,

Thanks for the code! The output of the query using your code and the
sample table (tblCodes) is:

C1 C2
A-01 EWK
A-02 EWK
A-04 ICP
A-05 ICP
A-01 TBN
A-02 TBN
A-03 TBN

I was hoping to find a query where specific IDs could be entered (2 or
more) and only the common codes would be returned. For example:

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

This code does not work, but you get the idea.

Using 2 IDs (i.e. A-01 and A-02), the query would return:

C1
TBN
EWK

Using 3 IDs (i.e. A-01, A-02, and A-03), the query would return:

C1
TBN

Not sure if it is possible, but I really appreciate your help so far.

Thanks!
Peter

[MGFoster]

pcb wrote:

Hi MG,

Thanks for the code! The output of the query using your code and the
sample table (tblCodes) is:

C1 C2
A-01 EWK
A-02 EWK
A-04 ICP
A-05 ICP
A-01 TBN
A-02 TBN
A-03 TBN

I was hoping to find a query where specific IDs could be entered (2 or
more) and only the common codes would be returned. For example:

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

This code does not work, but you get the idea.

Using 2 IDs (i.e. A-01 and A-02), the query would return:

C1
TBN
EWK

Using 3 IDs (i.e. A-01, A-02, and A-03), the query would return:

C1
TBN

Not sure if it is possible, but I really appreciate your help so far.

Thanks!
Peter

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

Try this:

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

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQxdG1oechKqOuFEgEQLrqQCguQJnOOqdYNmxaazlKTEFY8 3AAw0AoOi+
EnutTtclJ0UzVfaTfV5oGWVe
=Bi8w
-----END PGP SIGNATURE-----

[pcb]

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.

Peter

[MGFoster]

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/AwUBQxdhYIechKqOuFEgEQIttgCbB3V4B3ZYfHWhJzTBYHGbQh qjfZUAn2ms
Fp9vxDAfOHGxq+s2kTH2PGIa
=c7gO
-----END PGP SIGNATURE-----

[pcb]

Hi MG,

Yes! That works! Thank you very much. Now I have to figure out how to
implement it in VBA. But you have already spent more time than you
should have on somebody else's problem. Thank you again.

Peter