>Greetings.
>
>I have two tables that are identical in structure, but each contains
>data for different years.
>
>Here is the desc of the first table:
>
>mysql> desc counts2000;
>+-------------+-----------------------------+------+-----+---------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+-------------+-----------------------------+------+-----+---------+-------+
>| version | decimal(6,3) | YES | | NULL | |
>| sequence | tinyint(4) | YES | | NULL | |
>| dt_year | bigint(4) | YES | | NULL | |
>| dt_month | bigint(2) | YES | | NULL | |
>| dt_mon_name | varchar(10) binary | YES | | NULL | |
>| dt_day | bigint(2) unsigned zerofill | YES | | NULL | |
>| date_taken | date | YES | | NULL | |
>| exams | bigint(21) | | | 0 | |
>| dt_label | varchar(6) | YES | | NULL | |
>+-------------+-----------------------------+------+-----+---------+-------+
>9 rows in set (0.03 sec)
>
>The desc for the other is included at the end of the message. This
>first one has data for Jan/2000 - Jun/2000. The second has data for
>Jan/2001 - Apr/2001.
>
>I want to combine the two tables into one new one. Since they have
>a differing number of rows, but I want all of the rows from
>counts2000, I thought to use a JOIN. Here is the code I am trying
>to use:
>
>-------- code starts here ----------
>
>DROP TABLE IF EXISTS counts;
>
>CREATE TABLE counts SELECT
> AL1.sequence,
> AL1.dt_month,
> AL1.dt_day,
> AL1.dt_label,
> SUM(AL1.exams) AS y2000,
> SUM(AL2.exams) AS y2001
>FROM
> counts2000 AL1
>LEFT JOIN
> counts2001 AL2
>USING
> (sequence,dt_label)
>WHERE
> AL1.version >= '2' AND
> AL2.version >= '2'
>GROUP BY
> AL1.sequence,
> AL1.dt_label
>ORDER BY
> AL1.sequence,
> AL1.dt_month,
> AL1.dt_day;
>ALTER TABLE counts ADD INDEX sequence (sequence);
>ALTER TABLE counts ADD INDEX dt_month (dt_month);
>ALTER TABLE counts ADD INDEX dt_day (dt_day);
>
>-------- code ends here ----------
>
>Seems like it should work. It does run, but the goal behind using
>the JOIN (to get all of the rows in counts2000) is not being met. I
>only gets rows from both tables where the row exists in both tables.
>
>I am running MySQL 3.23.34 (for Windows) on Windows 2000 Professional.
>
>
>Here is some of the data from counts2000 that is not showing up in
>the resultant table:
>
>2.000 1 2000 5 May 01 2000-05-01 154 01-May
>2.000 1 2000 5 May 02 2000-05-02 283 02-May
>2.000 1 2000 5 May 03 2000-05-03 373 03-May
>2.000 1 2000 5 May 04 2000-05-04 298 04-May
>2.000 1 2000 5 May 05 2000-05-05 160 05-May
>2.000 1 2000 5 May 06 2000-05-06 117 06-May
>2.000 1 2000 5 May 07 2000-05-07 44 07-May
>2.000 1 2000 5 May 08 2000-05-08 308 08-May
>2.000 1 2000 5 May 09 2000-05-09 408 09-May
>
>Any ideas why the JOIN does not seem to be working?
>
>Thanks,
>Dan
>
>
>
>
>-------- SUPPORTING DOCS ---------------
>
>mysql> desc counts2001;
>+-------------+-----------------------------+------+-----+---------+-------+
>| Field | Type | Null | Key | Default | Extra |
>+-------------+-----------------------------+------+-----+---------+-------+
>| version | decimal(6,3) | YES | | NULL | |
>| sequence | tinyint(4) | YES | | NULL | |
>| dt_year | bigint(4) | YES | | NULL | |
>| dt_month | bigint(2) | YES | | NULL | |
>| dt_mon_name | varchar(10) binary | YES | | NULL | |
>| dt_day | bigint(2) unsigned zerofill | YES | | NULL | |
>| date_taken | date | YES | | NULL | |
>| exams | bigint(21) | | | 0 | |
>| dt_label | varchar(6) | YES | | NULL | |
>+-------------+-----------------------------+------+-----+---------+-------+
>9 rows in set (0.03 sec)
>
>Dan Huston
Sir, the join is working fine. That's the way it's supposed to work.
If the two tables have identical structure, then use a MERGE table to
combine them, and select from the MERGE table.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak
MySQL list magic words: sql query database
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
