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
Decision Support Systems
Cisco Networking Academy Program
Cisco Systems
2375 E. Camelback Rd., Ste. 220
Phoenix, AZ 85016
Phone (602) 778-2730
Cell Phone (602) 300-6043
