[vox-tech] A complicated SQL question

[Richard S. Crawford]

I have a number of rows in my database which look something like this:


id | username | canonicalname | password
---+----------+---------------+-----------------------
01 | asmith   | ART SMITH     | md5 encrypted password
02 | asmith1  | ART SMITH     | <NULL>
03 | bjones   | BILL JONES    | md5 encrypted password
04 | bjones12 | BILL JONES    | <NULL>
---+----------+---------------+-----------------------


There are something like 275 entries like these.  I'm having a hell of a time 
finding a way to select rows like 02 and 04, where canonicalname is a 
duplicate of the same value in another row and the password is blank, and 
then deleting those rows.  After executing my query, the table should look 
like this:


id | username | canonicalname | password
---+----------+---------------+-----------------------
01 | asmith   | ART SMITH     | md5 encrypted password
03 | bjones   | BILL JONES    | md5 encrypted password
---+----------+---------------+-----------------------


Is there a simple way to execute a single select query which would pull up all 
the rows I need?


-- 
Richard S. Crawford (http://www.mossroot.com)
Editor In Chief at Daikaijuzine (http://www.daikaijuzine.com)
Check out the Cthulhu Wiki: http://www.mossroot.com/cthulhuwiki
AIM: Buffalo2K / GTalk: underpope at gmail.com
Skype/Gizmo: underpope
"You can't trust your judgement when your imagination is out of focus."
		(Mark Twain)