List:General Discussion« Previous MessageNext Message »
From:Robert Covell Date:September 13 1999 12:33am
Subject:Join help...
View as plain text  
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

Thread
Join help...Robert Covell13 Sep
  • Re: Join help...Paul DuBois13 Sep