Description:
It appears that I am getting wrong results when I am running LEFT JOIN query on my table...
1. Checking number of rows in a table
mysql> select count(New_Customer_ID) from metafixCustomer;
+------------------------+
| count(New_Customer_ID) |
+------------------------+
| 3308 |
+------------------------+
1 row in set (0.00 sec)
2. Just to make sure that all New_Customer_ID records are unique.
mysql> select count(distinct New_Customer_ID) from metafixCustomer;
+---------------------------------+
| count(distinct New_Customer_ID) |
+---------------------------------+
| 3308 |
+---------------------------------+
1 row in set (0.07 sec)
3. Now, running LEFT JOIN Query
mysql> SELECT COUNT(a.New_Customer_ID) from metafixCustomer AS a LEFT JOIN valueList AS b ON (a.New_Customer_ID = b.IdNumber);
+--------------------------+
| COUNT(a.New_Customer_ID) |
+--------------------------+
| 3311 |
+--------------------------+
1 row in set (0.13 sec)
4. And now with distinct
mysql> SELECT COUNT(distinct a.New_Customer_ID) from metafixCustomer AS a LEFT JOIN valueList AS b ON (a.New_Customer_ID = b.IdNumber);
+-----------------------------------+
| COUNT(distinct a.New_Customer_ID) |
+-----------------------------------+
| 3308 |
+-----------------------------------+
1 row in set (0.20 sec)
It doesn't seem right, LEFT JOIN without distinct should not allow any duplication of records in my metafixCustomer table.
Any comments?
Thanks
How to repeat:
Attached
