From: Peter Brawley
Date: August 2 2007 2:47pm
Subject: Re: [mysql] reverse join
List-Archive: http://lists.mysql.com/mysql/208378
Message-Id: <46B1EE6F.3010609@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------050107030207090903040307"

--------------050107030207090903040307
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig

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

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

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;

PB

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?
>
>  
>
>  
>
> Thanks,
>
> Craig
> --------------------------------------------------------
> 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
>   

--------------050107030207090903040307--