mysql - Dreamweaver 中 mysql 记录集中的不明确错误

Question

每次我尝试测试 Dreamweaver 中的以下记录集时，都会抛出 1052 不明确错误。我知道它与 dateADDED 有关，但不知道如何解决。

    SELECT commentID, commentTitle, commentContent, topicTable.topicTitle,  DAYNAME(dateADDED) as day, MONTHNAME(dateADDED) as month, 
DAY(dateADDED) as date, YEAR(dateADDED) as year
FROM commentTable, topicTable
WHERE commentID = colname AND topicTable.topidID = commentTable.topicID

这是表格的布局，

CREATE TABLE userTable
(
userID VARCHAR(15) NOT NULL,
screenName VARCHAR(15) NOT NULL UNIQUE,
userPasswd CHAR(40) NOT NULL,
firstName VARCHAR(15) NOT NULL,
lastName VARCHAR(25) NOT NULL,
dateJoined TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
lastlogin DATETIME,
PRIMARY KEY(userID)
)
;

CREATE TABLE categoryTable                                          
(
categoryID MEDIUMINT AUTO_INCREMENT NOT NULL,                       
categoryName VARCHAR(30) NOT NULL,                                  
categoryDescription VARCHAR(200) NOT NULL,                          
PRIMARY KEY (categoryID)
)
;

CREATE TABLE topicTable                                             
(
topicID MEDIUMINT AUTO_INCREMENT NOT NULL,                          
topicTitle VARCHAR(30) NOT NULL,                                    
userID VARCHAR(15) NOT NULL,                                        
dateAdded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,             
categoryID MEDIUMINT NOT NULL,                                      
PRIMARY KEY (topicID)
)
;

CREATE TABLE commentTable                                           
(
commentID MEDIUMINT AUTO_INCREMENT NOT NULL,                        
commentTitle VARCHAR(30) NOT NULL,                                  
commentContent TEXT NOT NULL,                                       
userID VARCHAR(15) NOT NULL,                                        
dateAdded TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,             
topicID INT NOT NULL,                                               
PRIMARY KEY (commentID)
)
;

Answer 1

dateADDED 在两个表中都有表示，因此您应该从希望它出现在结果集中的一个中选择:

SELECT ct.commentID
     , ct.commentTitle
     , ct.commentContent
     , tt.topicTitle
     , DAYNAME(ct.dateADDED) as `day`
     , MONTHNAME(ct.dateADDED) as `month`
     , DAY(ct.dateADDED) as `date`
     , YEAR(ct.dateADDED) as `year`
FROM commentTable ct
JOIN topicTable tt ON ct.commentID = tt.colname  AND tt.topidID = ct.topicID

对了，还是想知道，colname代表什么，也许应该是ct.userID = tt.userID？