Sebastian Mendel wrote:
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)
)
Thanks for the hint. You query helped me along. Currently I have
something like
SELECT Q.subtable, Q.field, Q.id
FROM Q JOIN T ON T.subtable=Q.subtable and T.field=Q.field and T.val=Q.val
WHERE ID not in (SELECT distinct ID
FROM Q
LEFT OUTER JOIN T ON T.subtable=Q.subtable and T.field=Q.field and
T.val=Q.val
WHERE T.field is NULL)
which does a large part of the job.
Wkr,
--
Dr. Werner Van Belle
http://werner.sigtrans.org/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
