List:General Discussion« Previous MessageNext Message »
From:Yesmin Patwary Date:March 13 2006 2:31pm
Subject:Re: Compare lists Query?
View as plain text  
Dear All,
  
I had some issues in past with timestamp fields as a result I am unable to upgrade to
mysql 4.1 version.  I am sure below the query recommended by Josh works with 4.1 or
above. Would it be possible to rewrite this query for 3.23 version? 
  
Again, thank you Josh and all others for your kind help and comments.

Josh <josh2780@stripped> wrote:
  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 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.

		
---------------------------------
Yahoo! Mail
Bring photos to life! New PhotoMail  makes sharing a breeze. 
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