From: Peter Brawley Date: March 13 2006 2:56pm Subject: Re: Compare lists Query? List-Archive: http://lists.mysql.com/mysql/195719 Message-Id: <44158823.5040300@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-441588230EBE=======" --=======AVGMAIL-441588230EBE======= Content-Type: multipart/alternative; boundary=------------000209010806010104020607 --------------000209010806010104020607 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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 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 > --------------000209010806010104020607 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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

--------------000209010806010104020607-- --=======AVGMAIL-441588230EBE======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" 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 --=======AVGMAIL-441588230EBE=======--