List:General Discussion« Previous MessageNext Message »
From:Josh Date:March 10 2006 3:05pm
Subject:Re: Compare lists Query?
View as plain text  
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_id’s 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 
Thread
Compare lists Query?Yesmin Patwary10 Mar
  • Re: Compare lists Query?Josh10 Mar
    • Re: Compare lists Query?Yesmin Patwary13 Mar
      • Re: Compare lists Query?Peter Brawley13 Mar
      • Re: Compare lists Query?Josh13 Mar
        • Problems with timestamp field after upgrading MySQL Server.Yesmin Patwary13 Mar
          • Re: Problems with timestamp field after upgrading MySQL Server.Josh13 Mar
            • Re: Problems with timestamp field after upgrading MySQL Server.SGreen13 Mar
        • Problems with timestamp field after upgrading MySQL Server.Yesmin Patwary13 Mar
        • INSERT...SELECT Query Help Request.Yesmin Patwary22 Mar
          • Re: INSERT...SELECT Query Help Request.SGreen22 Mar