From: Baron Schwartz
Date: May 7 2007 12:11pm
Subject: Re: Help with Query
List-Archive: http://lists.mysql.com/mysql/206620
Message-Id: <463F178C.7050704@xaprb.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi Ed,

Ed Curtis wrote:
> I need to get some duplicate record information from a table and I 
> haven't found any way to do it yet. I figured there might be some type 
> of query I could do using a "for each" type command.
> 
> What I have is a table with names and companies. Some people have 
> multiple entries for different companies. What I need to get is the name 
> that has multiple entries along with the company names.
> 
> Name     |     Company
> ------------------------
> Joe Blow    Company 1
> Joe Blow    Company 2
> Joe G. Blow    Company 1
> 
> Running the query should only return Joe Blow with Company 1 and Company 2.
> 
> I can find out how many records Joe Blow has or list out each Company 
> record grouped by Name but I only want Names with multiple entries 
> shown. Can anyone help? I'm sure this also makes a difference but I'm 
> stuck using MySQL 3.23.

Since you are using 3.23, you can't use subqueries, which would otherwise make this 
easy (http://www.xaprb.com/blog/2006/10/09/how-to-find-duplicate-rows-with-sql/).  But 
you can do it with temporary tables instead:

CREATE TEMPORARY TABLE tmp AS SELECT Name, COUNT(*) AS cnt FROM tbl GROUP BY Name;
SELECT tbl.* FROM tbl JOIN tmp USING(Name) WHERE cnt > 1;

Baron