Chevy Mark Sunderland Wrote:
-------------------------------------------------------
>
> Try this please... thanks
Yes, I have tried - but this is a big difference.
In the first case rows are returned from the myfirsttablemysql table ONLY.
And there are only 54 rows meeting the condition in the WHERE clause.
Have you tried to run a query SELECT * instead SELECT COUNT(*) ?
SELECT *
FROM myfirsttablemysql
WHERE (YEAR(`myDatesYYYYMMDD`)='2011') ;
If you haven't, then do it and count how many rows the query retrieves.
In the second case you are performing a join on two tables - this is totally different query. It could return 0 rows, or 1000 rows, depending on how many rows meet the ON and WHERE conditions in the first and the second table.
Lets make your test case simpler:
mysql> delete from myfirsttablemysql where id <> 1
-> ;
Query OK, 125 rows affected (0.00 sec)
mysql> select * from myfirsttablemysql;
+----+-----------------+--------+
| id | myDatesYYYYMMDD | Region |
+----+-----------------+--------+
| 1 | 2011-12-21 | SOT1M |
+----+-----------------+--------+
1 row in set (0.00 sec)
mysql> SELECT
-> COUNT(*) `total`
-> FROM myfirsttablemysql
-> WHERE (YEAR(`myDatesYYYYMMDD`)='2011') ;
+-------+
| total |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
mysql> SELECT
-> COUNT(*) `total`
-> ,(y.Hour_1 + y.Hour_2)
-> FROM myfirsttablemysql x
-> JOIN mysecondtablemysql y ON y.Region = x.Region
-> WHERE (YEAR(`myDatesYYYYMMDD`)='2011')
-> GROUP BY y.Years;
+-------+-----------------------+
| total | (y.Hour_1 + y.Hour_2) |
+-------+-----------------------+
| 2 | 14857 |
+-------+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT *
-> FROM myfirsttablemysql x
-> JOIN mysecondtablemysql y ON y.Region = x.Region
-> WHERE (YEAR(`myDatesYYYYMMDD`)='2011') ;
+----+-----------------+--------+--------+--------+--------+--------+-------+----+
| id | myDatesYYYYMMDD | Region | Region | Hour_1 | Hour_2 | Months | Years | ID |
+----+-----------------+--------+--------+--------+--------+--------+-------+----+
| 1 | 2011-12-21 | SOT1M | SOT1M | 9035 | 5822 | 1 | 2011 | 25 |
| 1 | 2011-12-21 | SOT1M | SOT1M | 8911 | 6221 | 2 | 2011 | 42 |
+----+-----------------+--------+--------+--------+--------+--------+-------+----+
2 rows in set (0.00 sec)
Do you see the reason now ?
There is only ONE record in the first table.
But in the second table there are TWO corresponding records that have the column
Region = 1 and year = 2011. Therefore MySql joins the 1 record from the 1st table to 2 records from the 2nd table and produces 2 combined records in the output.
And if you count number of records of the output, you will get 2, not 1, becouse the query returns 2 rows, not 1.
This is a way how a join works. It combines recors from two tables.
Imagine the case that there are two tables both having 5 records.
Is it possible that a join returns 25 rows even when there are only 5 rows in the first table ?
mysql> create table tab1( x int );
Query OK, 0 rows affected (0.17 sec)
mysql> insert into tab1 values (1), (2), (3),(4),(5);
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> create table tab2( y int );
Query OK, 0 rows affected (0.10 sec)
mysql> insert into tab2 values (1), (2), (3),(4),(5);
Query OK, 5 rows affected (0.08 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select count(*)
-> from tab1 join tab2 on 1=1;
+----------+
| count(*) |
+----------+
| 25 |
+----------+
1 row in set (0.01 sec)
mysql> select * from tab1 join tab2 on 1=1;
+------+------+
| x | y |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
| 1 | 3 |
| 2 | 3 |
| 3 | 3 |
| 4 | 3 |
| 5 | 3 |
| 1 | 4 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 5 | 4 |
| 1 | 5 |
| 2 | 5 |
| 3 | 5 |
| 4 | 5 |
| 5 | 5 |
+------+------+
25 rows in set (0.00 sec)
Edited 1 time(s). Last edit at 02/26/2012 09:30AM by irek kordirko.