469,628 Members | 1,101 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,628 developers. It's quick & easy.

Finding Duplicates Help

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.

Jul 11 '06 #1
2 2020
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.
Jul 11 '06 #2
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.
Jul 11 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Erich | last post: by
reply views Thread by Timo Nentwig | last post: by
6 posts views Thread by Maxi | last post: by
Thekid
3 posts views Thread by Thekid | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.