Rene Churchill <[EMAIL PROTECTED]> wrote on 02/22/2005 04:23:47 PM:
> [EMAIL PROTECTED] wrote:
> > Rene Churchill <[EMAIL PROTECTED]> wrote on 02/22/2005 03:39:05
PM:
> > > Hi Shawn,
> > >
> > > This is what I wound up going with:
> > >
> > > SELECT b.id,
> > > if(a.a <=> b.a, NULL, b.a),
> > > if(a.b <=> b.b, NULL, b.b),
> > > if(a.c <=> b.c, NULL, b.c),
> > > (NOT (a.a <=> b.a) AND
> > > (a.b <=> b.b) AND
> > > (a.c <=> b.c)) as DiffTest
> > > FROM testC as c
> > > LEFT JOIN testB as b ON c.id = b.id
> > > LEFT JOIN testA as a ON c.id = a.id
> > > HAVING DiffTest = 1
> > >
> > > Your query runs twice as fast as this one, but all of the
> > > if()'s in the SELECT portion allow me to determine which
> > > columns were different between the two tables, which is
> > > important for the next step of the process that I'm
> > > working with.
> > >
> > > Thanks,
> > > Rene
> > >
> >
> > Why not do both? I only had the one term in my SELECT clause because
I
> > didn't know what other data you needed. Try this as a hybrid:
> >
> > SELECT b.id,
> > if(a.a <=> b.a, NULL, b.a),
> > if(a.b <=> b.b, NULL, b.b),
> > if(a.c <=> b.c, NULL, b.c),
> > ISNULL(a.id) as DiffTest
> > FROM testC c
> > INNER JOIN testB b
> > on b.id = c.id
> > LEFT JOIN testA a
> > on a.id = b.id
> > AND a.a <=> b.a
> > AND a.b <=> b.b
> > AND a.c <=> b.c
> >
> > That way you get both the speed of a direct comparison (and tighter
> > joins) and the additional information you need for your next step.
>
> I don't believe that will work because if a.id is NULL then so
> are a.a, a.b and a.c since the LEFT JOIN failed it's conditions.
> That would flag all three columns as being different, even if
> only one of them was.
>
> In the example data I gave, all of the rows were missing from
> table testA, but that was just a boiled down test case to
> demonstrate the problems I was having between the WHERE and
> HAVING clauses.
>
> Rene
>
> --
> Rene Churchill Specializing in Internet Databases,
> Astute Computing, LLC. Custom Programming and
> Phone: 802-244-5151 Webmaster support
> Fax: 802-244-5512
> http://www.astutecomputing.com
OK, That's a different assumption than I was working from before. I
thought you wanted to flag the whole row as being different if any field
between them was different or if the row was new. But, then, you said you
needed to detect the diffs. I guess I need to slow down and read before I
respond next time ;-)
Hmmmm..... After re-reviewing your earlier post. I wonder if we don't need
to move the NOT to the outside of the parentheses.... You see, I am not
completely sure of the order of operations... Will the ANDs happen first
then the NOT or will the engine NOT the first term then AND the NOT-ed
results with the other terms.... I am just not sure. If we write it this
way, there can be no doubt:
SELECT b.id,
if(a.a <=> b.a, NULL, b.a),
if(a.b <=> b.b, NULL, b.b),
if(a.c <=> b.c, NULL, b.c),
(NOT ((a.a <=> b.a) AND
(a.b <=> b.b) AND
(a.c <=> b.c))) as DiffTest
FROM testC as c
LEFT JOIN testB as b ON c.id = b.id
LEFT JOIN testA as a ON c.id = a.id
HAVING DiffTest = 1
Well, I am stumped trying to make it any faster. Maybe changing the first
LEFT JOIN to an INNER JOIN, or caching the results of the first JOIN in a
temp table (splitting up the query into two steps). Other than that, I
think we have it as good as we are going to get it.
Know what? I just realized we should have been CC:-ing the list this whole
time. That way everyone else would have been able to contribute and learn,
too.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
