Adam wrote:
All,
I've got data listing people's identification details including first and last name, social, and status in the application. Given this data, I want to know how many duplicate socials I have. Normally I'd write a query with a sub select to get that information - something like this:
SELECT a.*
FROM table a
WHERE a.ssn IN (SELECT b.ssn FROM table b GROUP BY b.ssn HAVING count(b.ssn) > 1);
However, I don't have MySQL 4.1.x so the sub select is out of the question. I've decided to identify the duplicate socials and dump them into a table. Join the table with the dups to the table storing the identification details and then drop the dups table.
However, its not working.
From the first statement (see below) I am getting an error reading
ERROR 1060 at line 3: Duplicate column name 'ssn'
WTF?
############################################################ # SQL Statements ############################################################
CREATE TABLE dups (ssn VARCHAR(25) NULL) SELECT s.ssn FROM org s GROUP BY s.ssn HAVING COUNT(s.ssn) > 1;
MySQL automatically creates the columns in the SELECT part of CREATE...SELECT, so this statement tries to define two ssn columns, one you explicitly defined and the other from the SELECT. See the manual <http://dev.mysql.com/doc/mysql/en/CREATE_TABLE.html> for more. Also, you probably want a temporary table for this. So, I think you want
CREATE TEMPORARY TABLE dups SELECT ssn FROM org GROUP BY ssn HAVING COUNT(*) > 1;
SELECT s.*
FROM org s, tbl_tmp_ssn t
WHERE s.ssn = t.ssn;
What is tbl_tmp_ssn? Shouldn't this be
SELECT s.* FROM org s, dups t WHERE s.ssn = t.ssn;
DROP TABLE dups;
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
