Dear All,
First of all, I would like to thank to Josh and Peter Brawley for their kind
help on previous issue.
Here is another Issue:
There are many tables that have timestamp field with 8 char (yyyymmdd). I use
this format all over our website and to create various reports. Below is
static php code that I used numerous places:
<?
$year=substr("$date", 0, 4);
$month=substr("$date", 4, 2);
$day=substr("$date", 6, 2);
?>
All timestamp dependent reports and web pages had problems, once I upgrade to
MySQL 4.1.12. At the end I had to downgrade to 3.23 to bring web site
operation to normal. Is there anyway to preserve old timestamp format after
upgrading?
MySQL - 3.23
+--------+------------+----------
| Field | Type | Default (yyyymmdd)
+--------+------------+----------
|log_date|timestamp(8)| 00000000
MySQL - 4.1.12
+--------+-----------+--------------------
| Field | Type | Default
+--------+-----------+--------------------
|log_date| timestamp |0000-00-00 00:00:00
Josh <[EMAIL PROTECTED]> wrote:
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 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
>
> --- 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]
---------------------------------
Yahoo! Mail
Use Photomail to share photos without annoying attachments.
