Re: Compare lists Query?
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 [EMAIL PROTECTED] 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_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. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze.
Re: Compare lists Query?
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 [EMAIL PROTECTED] 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_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. - 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 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compare lists Query?
You could rotate the output... basically get 1 row with 11 columns (CA01_count,CA02_count,...,CA12_count) (leaving out CA10) joining all 12 tables together... Or... perhaps we can help with the timestamp issues you are having and get you upgraded to later version of mysql that supports nested SELECT statements. --- Yesmin Patwary [EMAIL PROTECTED] 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 [EMAIL PROTECTED] 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_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. - Yahoo! Mail Bring photos to life! New PhotoMail makes sharing a breeze. __ 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]
Compare lists Query?
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!
Re: Compare lists Query?
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]
