Hi Shawn, all !
Shawn Cummings wrote (re-formatted):
If I have (2) tables. Like
GROUP , CODE
GRP1, AA
GRP1, AB
GRP1, AC
GRP2, BA
GRP2, BB
GRP2, BC
And;
USER , UCODE
ME, AA
ME, AC
YOU, AA
What's the best way to query to find out if "ME" has all codes in either of
the groups?
In other words, I want to know if I have all (3) CODEs required for GRP1,
and all (3) CODEs required for GRP2?
I have two ideas about possible approaches, but I leave the details to you:
a) If you are sure your "UCODE" values are a subset of the "CODE"
values, then counting might be enough:
SELECT COUNT (DISTINCT UCODE) FROM tab2 WHERE USER = "ME";
SELECT `GROUP`, COUNT (DISTINCT CODE) FROM tab1 GROUP BY `GROUP`;
Then, compare the values: If there cannot be any UCODE values which do
not appear as CODE values, then equal counts imply full coverage.
b) If you are not sure, my only idea is to loop over all "GROUP" values
and do full outer joins on "CODE" / "UCODE":
CREATE TEMPORARY TABLE tmp1 (UCODE datatype, FOUND SMALLINT)
AS SELECT DISTINCT UCODE, 1 FROM tab2 WHERE USER = "ME";
SELECT DISTINCT `GROUP` FROM tab1;
for all GRP values returned
do
SELECT tab1.`GROUP`, tmp1.FOUND FROM tab1 LEFT OUTER JOIN tmp1
ON (tab1.CODE = tmp1.UCODE)
WHERE tmp1.FOUND IS NULL
UNION
SELECT tab1.`GROUP`, tmp1.FOUND FROM tab1 RIGHT OUTER JOIN tmp1
ON (tab1.CODE = tab2.UCODE)
WHERE tab1.`GROUP` IS NULL;
done
If this UNION is empty, it means that neither tab1 (for this GRP value)
nor tmp1 (which represents "ME") had a row without a match in the other
table.
Sorry, I have no proposal which is more elegant than these two.
Jörg
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
