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 <yesmin25@stripped> 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
