List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:March 13 2006 2:56pm
Subject:Re: Compare lists Query?
View as plain text  
Yesmin Patwary wrote:
> 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
>   
SELECT cl1.list_name, COUNT(*) AS count
FROM customerList cl1
INNER JOIN customerList cl2 USING (id)
WHERE cl1.name = 'CA10' AND cl2.name != 'CA10'
GROUP BY cl1.list_name;

PB

-----
>   
> --- 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. 
>   
> ------------------------------------------------------------------------
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006
>   

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.1/279 - Release Date: 3/10/2006
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