Here's one method: SELECT cl1.list_name, count(*) as count FROM customerList cl1 WHERE cl1.id IN (SELECT cl2.id FROM customerList cl2 WHERE cl2.list_name='CA10') and cl1.list_name != 'CA10' GROUP BY cl1.list_name
--- Yesmin Patwary <[EMAIL PROTECTED]> wrote: > Good morning all, > > We have 12 customer lists: CA01, CA02, .,CA12. > > Table: customerList > +-----------+------+ > | list_name | id | > +-----------+------+ > | CA10 | 20BE | > | CA07 | 20BE | > | CA11 | 20BE | > | CA03 | 20BE | > | CA10 | NQCR | > | CA04 | NQCR | > | CA02 | MVYK | > | CA10 | 0BEC | > | AND SO ON. | > +-----------+------+ > > Each list has 25 to 350 customers. Same > customer_id may exist in multiple lists. We need to > compare CA10 list customer_ids with other 11 lists > to find matching id count by list_name. The query > output should be something similar below: > +------+-----------+ > | list_name |count | > +------+-----------+ > | CA05 | 60 | > | CA07 | 42 | > | CA01 | 35 | > | CA03 | 28 | > | CA09 | 15 | > | AND SO ON | > +-----------+------+ > > Can this be done with a SELECT statement without > using perl or php? > > Thanks in advance for any help. > > > --------------------------------- > Relax. Yahoo! Mail virus scanning helps detect nasty viruses! __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]