JOIN with single table? 1

[admoore]

I have an application that stores email subscribers to various lists in a single table.

I wish to remove records from one list which have a duplicate address in a second list. I would find this easy using a join if the lists were in separate tables; however, since the lists are in a single table and identified by a listid field, I am unsure of how to proceed.

The two lists are listid 103 and listid 178. I wish to remove all records from listid 178 which have a matching emailaddress field in listid 103.

I determined that of the 29,635 total records only 20,203 are distinct using

SELECT DISTINCT(emailaddress)
FROM email_list_subscribers
WHERE
`listid` = '103' OR `listid` = '178'

Any suggestion on how I might accomplish this would be appreciated...

 

[r937]

I wish to remove all records from listid 178 which have a matching emailaddress field in listid 103.

DELETE list178
  FROM email_list_subscribers AS list178
INNER
  JOIN email_list_subscribers AS list103
    ON list103.emailaddress = list178.emailaddress
   AND list103.listid = 103
 WHERE list178.listid = 178

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon