INNER JOIN的Where子句不起作用

I have a quick question, so based on my research of MySql JOINS I understand that this query

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

Should display only items from the two tables if their ruleID's match and the accountID in the Rule table is geoskygps@gmail.com, but what happens for me is I get results from all accountID not just geoskygps@gmail.com.

I also tried using the AND clause instead of WHERE but I get the same exact results.

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

If someone could point me in the right direction I would greatly appreciated it.

Thanks, Mike this is for: 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 |

This is for 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 |

Rule is a reserved keyword in SQL. Take a look at this: https://msdn.microsoft.com/en-us/library/ms189822%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

You could work around setting Rule in single quotes => 'Rule'.ruleID and so on but i strongly recommend to rename it.

Hi so I just tried the below. Your Code appeared to function as expected for the rough tables I created. Potentially the issue is in the data which you are using?

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'

returns the following:

ruleID | accountID           |  data |  ruleID |    accountID | data

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

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

So I figured it out. Here is the proper query.

I had to add a second parameter to my WHERE clause since both the databases hold multiples of the same record.

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

Thanks for the input guys.