Problem designing a query to select shared IDs

[Guest]

Hi,

I'm having trouble designing a query. The set-up is as follows:
[* = PK]
tblOrg (OrgID*, OrgName)
tblProg (ProgID*, ProgName)
tblOrgProg(OrgID*, ProgID*)

I'd like to select one or more ProgIDs (using a multi-select list on a form)
and then display the OrgIDs from tblOrgProg that have a count = (number of
ProgIDs selected) and pair with the selected ProgID(s) only.
e.g.
tblOrgProg
=========
OrgID, ProgID
---------------
1, A
2, A
3, A
1, B
2, B
3, B
1, C
2, C
4, C

If I select (A, B, C) then the query returns (1, 2)
If I select (A, B) then the query returns (3)
If I select (C), then the query returns (4)

This is a follow-up of the question I asked a while ago:

http://support.microsoft.com/newsgr...68b4a85fb9f7&lang=en&cr=US&sloc=en-us&m=1&p=1

and I've tried modifying the query that Van provided, but without the
expected result. I tried it for 2 ProgIDs by having 2 parameters, Prog1 and
Prog2, and changing the count to count(*) = 2, and adding an AND clause to
the WHERE part to check if the ProgID matches Prog1 or Prog2.

Will appreciate any help on this.
Thanks.

-Amit

 

[Michel Walsh]

Hi,



SELECT OrgID
FROM orgProg LEFT JOIN specs
ON orgProg.ProgID = specs.ProgID
GROUP BY OrgID
HAVING COUNT(org.ProgID) = COUNT(specs.ProgID)



Assuming table Specs has one field, ProgID, one record per ProgID value you
want (SQL works with table, not very well with ListBox list... SQL tables
can supply list, but the reverse is not immediate). I also assume you are
able to make a (temporary) table out of your multi-selection list.


The idea behind the query is the "not in" construction using outer join. A
"group" having a NULL in the unpreserved side is not to be kept. On the
other hand, a group has also to get ALL the required ProgID, so, since
COUNT(fieldName) does NOT count the NULL, making use of that observation on
the outer join solved the problem.

Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


change it to

SELECT OrgID
FROM orgProg LEFT JOIN specs
ON orgProg.ProgID = specs.ProgID
GROUP BY OrgID
HAVING COUNT(org.ProgID) = (SELECT COUNT(ProgID) FROM specs)


Vanderghast, Access MVP

Hi,



SELECT OrgID
FROM orgProg LEFT JOIN specs
ON orgProg.ProgID = specs.ProgID
GROUP BY OrgID
HAVING COUNT(org.ProgID) = COUNT(specs.ProgID)



Assuming table Specs has one field, ProgID, one record per ProgID value
you want (SQL works with table, not very well with ListBox list... SQL
tables can supply list, but the reverse is not immediate). I also assume
you are able to make a (temporary) table out of your multi-selection list.


The idea behind the query is the "not in" construction using outer join.
A "group" having a NULL in the unpreserved side is not to be kept. On the
other hand, a group has also to get ALL the required ProgID, so, since
COUNT(fieldName) does NOT count the NULL, making use of that observation
on the outer join solved the problem.

Hoping it may help,
Vanderghast, Access MVP

Hi,

I'm having trouble designing a query. The set-up is as follows:
[* = PK]
tblOrg (OrgID*, OrgName)
tblProg (ProgID*, ProgName)
tblOrgProg(OrgID*, ProgID*)

I'd like to select one or more ProgIDs (using a multi-select list on a
form)
and then display the OrgIDs from tblOrgProg that have a count = (number
of
ProgIDs selected) and pair with the selected ProgID(s) only.
e.g.
tblOrgProg
=========
OrgID, ProgID
---------------
1, A
2, A
3, A
1, B
2, B
3, B
1, C
2, C
4, C

If I select (A, B, C) then the query returns (1, 2)
If I select (A, B) then the query returns (3)
If I select (C), then the query returns (4)

This is a follow-up of the question I asked a while ago:

http://support.microsoft.com/newsgr...68b4a85fb9f7&lang=en&cr=US&sloc=en-us&m=1&p=1

and I've tried modifying the query that Van provided, but without the
expected result. I tried it for 2 ProgIDs by having 2 parameters, Prog1
and
Prog2, and changing the count to count(*) = 2, and adding an AND clause
to
the WHERE part to check if the ProgID matches Prog1 or Prog2.

Will appreciate any help on this.
Thanks.

-Amit

 

[Michel Walsh]

Hi,


I hope this one is without typo....

SELECT OrgID
FROM orgProg LEFT JOIN specs
ON orgProg.ProgID = specs.ProgID
GROUP BY OrgID
HAVING COUNT(orgProg.ProgID) = (SELECT COUNT(ProgID) FROM specs)


Vanderghast, Access MVP

 

[Guest]

Hi,


I hope this one is without typo....

SELECT OrgID
FROM orgProg LEFT JOIN specs
ON orgProg.ProgID = specs.ProgID
GROUP BY OrgID
HAVING COUNT(orgProg.ProgID) = (SELECT COUNT(ProgID) FROM specs)


Vanderghast, Access MVP

Hi Michel,

Thanks for your response and your efforts to design the query.

The query above is not quite working the way I want it to.
What I'm getting as a result is the list of all the OrgIDs that occur twice
in the tblOrgProg, and with either of the ProgIDs specified as parameter. So,
if I specified A and C as ProgIDs, I'm getting OrgIDs that work with A and B,
A and D, B and C and so on, whereas what I want is OrgIDs that work *only*
with A and C. So, that means the count (OrgID) in tblOrgProg is 2 (or the
count of records in the temp table containing the ProgIDs - for this example,
I created a temp table with 2 ProgIDs as parameter) and the ProgIDs for both
occurrences of the (same) OrgID in tblOrgProg are equal to what's in the temp
table.

Hope this makes sense.

thanks.

-Amit

 

[Michel Walsh]

Hi,

Indeed. I didn't checked that possibility in the previous reply.

What about adding, to the HAVING clause:



AND COUNT(*)=COUNT(Specs.ProgID)



or, if you prefer:

SELECT OrgID
FROM orgProg LEFT JOIN specs
ON orgProg.ProgID = specs.ProgID
GROUP BY OrgID
HAVING COUNT(*) = (SELECT COUNT(ProgID) FROM specs)
AND COUNT(*) = COUNT(specs.ProID)




Hoping it may help,
Vanderghast, Access MVP