Finding Duplicates Help

[duende]

I was hoping someone will be able to help me, i've got a bit of
experience with mysql, but nothing quite like this.

I have a table with 3 columns 'sid', 'aid', and 'call'.

What i want to do is to create a query that shows me all records where
sid matches and aid matches but call differs.

For example, it would give me this result:

sid aid call
abc def gh
abc def ij

and not:

sid aid call
abc def gh
abc def gh

Currently i have a query that gives me all matches of sid's and aid's
but gives me call's that match and don't match.

I hope this is not too confusing, I'm really lost at this point. Thank
you.

[duende]

I seem to be very close now, here is what I have:

select a.sid, a.aid, a.call from raw as a, raw as b where a.aid=b.aid
and a.sid=b.sid and a.call != b.call;

This appears to work ( hard to verify fully due to over 100,000
records), however, one part that doesn't work is that if there are two
records with matching sid's and aid's but one of the call's are blank,
it won't pick it up. You would think that comparing two values, if one
has a value, and the other one doesn't, they wouldn't match.
duende wrote:

I was hoping someone will be able to help me, i've got a bit of
experience with mysql, but nothing quite like this.

I have a table with 3 columns 'sid', 'aid', and 'call'.

What i want to do is to create a query that shows me all records where
sid matches and aid matches but call differs.

For example, it would give me this result:

sid aid call
abc def gh
abc def ij

and not:

sid aid call
abc def gh
abc def gh

Currently i have a query that gives me all matches of sid's and aid's
but gives me call's that match and don't match.

I hope this is not too confusing, I'm really lost at this point. Thank
you.

[Bill Karwin]

duende wrote:

I seem to be very close now, here is what I have:

select a.sid, a.aid, a.call from raw as a, raw as b where a.aid=b.aid
and a.sid=b.sid and a.call != b.call;

This appears to work ( hard to verify fully due to over 100,000
records), however, one part that doesn't work is that if there are two
records with matching sid's and aid's but one of the call's are blank,
it won't pick it up. You would think that comparing two values, if one
has a value, and the other one doesn't, they wouldn't match.

That behavior has been part of the standard SQL language forever. The
idea is that NULL is the absence of a value, so how can we know if it is
equal or unequal to anything?

"What's Bill's birthday?"
"I don't know."
"Is it the same as Duende's birthday?"
"I can't say for certain."

Anyway, you could try this alternative:

select a.sid, a.aid, a.call
from raw as a, raw as b
where a.aid=b.aid and a.sid=b.sid and (a.call != b.call or a.call IS
NULL or b.call IS NULL);

Regards,
Bill K.