From: Werner Van Belle (wernersigtrans.org)
Date: Tue Jun 10 2008 - 02:48:50 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]