List:General Discussion« Previous MessageNext Message »
From:Craig (OFT) Weston Date:August 2 2007 6:01pm
Subject:RE: [mysql] reverse join
View as plain text  
 Thanks for your help. 


From: Peter Brawley [mailto:peter.brawley@stripped] 
Sent: Thursday, August 02, 2007 10:47 AM
To: Weston, Craig (OFT); mysql@stripped
Subject: Re: [mysql] reverse join



>How do I pull out all the non-duplicate records?

With an exclusion join. To find non-duplicates with respect to one

SELECT keycandidate
FROM tbl t1
LEFT JOIN tbl t2 ON t1.keycandidate=t2.keycandidate
WHERE t2.keycandidate IS NULL;

To expand the comparison to multiple keys...

SELECT key1, key2, ...
FROM tbl t1
LEFT JOIN tbl t2 ON t1.key1=t2.key2 AND t1.key2=t2.key2 AND ...
WHERE t2.key1 IS NULL;


Weston, Craig (OFT) wrote: 

Hi there, 
            I have a situation where I have duplicate data within
several tables, but they are all indexed in the same way.  How do I pull
out all the non-duplicate records?
Would I have to create a temporary table, add one data set, then add the
second where it doesn't equal the first, and then do the same with the
third? Or is there a cleaner way?
This e-mail, including any attachments, may be confidential, privileged
or otherwise legally protected. It is intended only for the addressee.
If you received this e-mail in error or from someone who was not
authorized to send it to you, do not disseminate, copy or otherwise use
this e-mail or its attachments.  Please notify the sender immediately by
reply e-mail and delete the e-mail from your system.


No virus found in this incoming message.
Checked by AVG Free Edition. 
Version: 7.5.476 / Virus Database: 269.11.2/931 - Release Date: 8/1/2007
4:53 PM

[mysql] reverse joinCraig \(OFT\) Weston2 Aug
  • Re: [mysql] reverse joinPeter Brawley2 Aug
    • RE: [mysql] reverse joinOFT)2 Aug