Werner Van Belle schrieb:
Hello,
You might find the following challenging -or- you might now the answer :-)
Table Q
Subtable, field, val, ID
A 1 a 42
A 2 b 42
B 1 a 78
B 2 t 78
B 3 o 78
C 1 u 23
Table R
Subtableid, field, val
A 1 a
A 2 b
Table S
Subtableid, field, val
B 1 a
B 2 t
Table T
Subtableid, field, val
C 1 u
A 1 a
A 2 b
We now want to check whether table R is fully contained in table Q and
what the ID is. In this case the answer should be 42. However if we
would use table S and mathc it against table Q, then we should not get
78 back since field 3 is missing in table S.
Also, we might want to perform this operation in batch mode, where we
provide a table such as T for which we then should get the return value
Subtable, field, val, ID
A 1 a 42
A 2 b 42
C 1 u 23
Is there anybody that bumped into a similar query and was able to solve
it satisfactory ?
you can do a OUTER JOIN on subtableid, and than check for NULL values (with
HAVING), which means that at least one field is missing in one of the tables
with sub selects:
untested:
SELECT Subtable, ID
FROM `T`
WHERE ID NOT IN (
SELECT ID
FROM T
OUTER JOIN Q
ON T.Subtable = Q.Subtable
AND T.field = Q.field
AND T.val = Q.val
HAVING ISNULL(Q.ID)
OR ISNULL(T.ID)
)
--
Sebastian Mendel
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
