Could someone help me make the join(left I believe is what I need) for the
following two tables.
Desired Result: A list of all the pageAlias's and the count for today
even if it (the count) doesn't exist. In the code I would check (count)
and if it was null (what a left join adds if the row is not there) display
0;
This is what I have now and I get ERROR 1066: Not unique table/alias:
'pageDayHitLog1':
select pageList1.pid, pageList1.pageAlias from pageDayHitLog1 LEFT JOIN
pageDayHitLog1 ON pageList1.pid=pageDayHitLog1.pid where
YEAR(pageDayHitLog1.hitDate)=YEAR(NOW()) AND
MONTH(pageDayHitLog1.hitDate)=MONTH(NOW()) AND
DAYOFMONTH(pageDayHitLog1.hitDate)=DAYOFMONTH(NOW()) and
pageList1.pid=pageDayHitLog1.pid;
mysql> EXPLAIN pageList1;
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| pid | int(11) | | PRI | 0 | auto_increment |
| pageAlias | varchar(40) | | MUL | | |
| sid | int(11) | YES | | NULL | |
| beingTracked | enum('Y','N') | YES | | Y | |
| trackable | enum('Y','N') | YES | | Y | |
| extraTrack | enum('Y','N') | YES | | N | |
+--------------+---------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
mysql> explain pageDayHitLog1;
+---------+---------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+------------+----------------+
| did | int(11) | | PRI | 0 | auto_increment |
| pid | int(11) | | PRI | 0 | |
| count | int(11) | YES | | 0 | |
| hitDate | date | | MUL | 0000-00-00 | |
+---------+---------+------+-----+------------+----------------+
4 rows in set (0.00 sec)
Thanks,
-Robert
