首页 文章

INNER JOIN的Where子句不起作用

提问于
浏览 次
1

我有一个简单的问题，所以根据我对MySql JOINS的研究，我理解这个查询

SELECT *
FROM Rule
INNER JOIN RuleList
ON Rule.ruleID = RuleList.ruleID
WHERE Rule.accountID = 'geoskygps@gmail.com'

如果他们的 ruleID 的匹配和规则表中的 accountID 是 geoskygps@gmail.com ，则应仅显示两个表中的项目，但对我来说，我得到的结果是 accountID 而不仅仅是 geoskygps@gmail.com .

我也尝试使用 AND 子句而不是 WHERE 但我得到了完全相同的结果 .

SELECT *
FROM Rule
INNER JOIN RuleList
WHERE Rule.ruleID = RuleList.ruleID
AND Rule.accountID = 'geoskygps@gmail.com'

如果有人能指出我正确的方向，我会非常感激 .

谢谢，Mike这是为了：show create Table Rule; | Rule | CREATE TABLE Rule ( accountID varchar(32) NOT NULL, ruleID varchar(32) NOT NULL, isCronRule tinyint(4) DEFAULT NULL, ruleTag varchar(24) DEFAULT NULL, selector text, actionMask int(10) unsigned DEFAULT NULL, cannedActions varchar(80) DEFAULT NULL, priority smallint(5) unsigned DEFAULT NULL, minNotifyAge int(10) unsigned DEFAULT NULL, triggerReset smallint(5) unsigned DEFAULT NULL, notifyEmail varchar(128) DEFAULT NULL, emailSubject text CHARACTER SET utf8, emailText text CHARACTER SET utf8, smsText text CHARACTER SET utf8, useEmailWrapper tinyint(4) DEFAULT NULL, ruleDisable varchar(64) DEFAULT NULL, ruleEnable varchar(64) DEFAULT NULL, sendCommand text, isActive tinyint(4) DEFAULT NULL, description varchar(128) CHARACTER SET utf8 DEFAULT NULL, lastUpdateTime int(10) unsigned DEFAULT NULL, creationTime int(10) unsigned DEFAULT NULL, PRIMARY KEY (accountID,ruleID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

这是针对RuleList | RuleList | CREATE TABLE RuleList ( accountID varchar(32) NOT NULL, deviceID varchar(32) NOT NULL, groupID varchar(32) NOT NULL, statusCode int(10) unsigned NOT NULL, ruleID varchar(32) NOT NULL, lastUpdateTime int(10) unsigned DEFAULT NULL, creationTime int(10) unsigned DEFAULT NULL, PRIMARY KEY (accountID,deviceID,groupID,statusCode,ruleID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

php mysql

3 回答

  • 0

    嗨所以我只是尝试了以下 . 您的代码似乎按照我创建的粗略表格的预期运行 . 问题可能在于您使用的数据？

    create table Rule(ruleID INTEGER,accountID text,data text);
insert into Rule values(1, 'geoskygps@gmail.com' , 'data');
insert into Rule values(2, 'nothing@gmail.com' , 'data');
insert into Rule values(3, 'geoskygps@gmail.com' , 'data');

create table Rulelist(ruleID INTEGER,accountID text,data text);
insert into Rulelist values(1, 'geoskygps@gmail.com' , 'data1');
insert into Rulelist values(2, 'geoskygps@gmail.com' , 'data1');
insert into Rulelist values(3, 'geoskygps@gmail.com' , 'data1');

select * FROM Rule
JOIN RUlelist
ON rule.ruleID = RuleList.ruleID
WHERE Rule.accountID = 'geoskygps@gmail.com'

    返回以下内容：

    ruleID | accountID           |  data |  ruleID |    accountID | data

1      | geoskygps@gmail.com |  data |  1      |    data      | data1

3      | geoskygps@gmail.com |  data |  3      |    data      | data1
    回复于
  • 0

    所以我明白了 . 这是正确的查询 .

    我必须在我的WHERE子句中添加第二个参数，因为两个数据库都拥有相同记录的倍数 .

    "SELECT * 
 FROM Rule 
 JOIN RuleList 
 ON Rule.ruleID = RuleList.ruleID 
 WHERE Rule.accountID = '$login_user' and RuleList.accountID ='$login_user';

    感谢输入的人 .

    回复于
  • -1

    Rule是SQL中的保留关键字 . 看看这个：https://msdn.microsoft.com/en-us/library/ms189822%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

    您可以使用单引号设置规则=> 'Rule'.ruleID 等等，但强烈建议您重命名它 .

    回复于

相关问题