getting data from 2 tables in inner join

Question

I am not new in inner join but i don't know why i am getting data from two tables

Look i have some tables

Table One

select TransactionMId,ModelNumber,EmeiNo,Color from tblTransactionD 

TransactionMId ModelNumber EmeiNo Color

c174f2c0-72f4-4746-b83d-21ffca6a5a61 620G 10001 grey

c174f2c0-72f4-4746-b83d-21ffca6a5a61 ONE 10005 black

4fa11531-886a-4d15-af5c-7355cc3648f5 620G 10001 grey

Table Two

select TransactionMId,ModelNumber,EmeiNo,Color from tblTransactionP

TransactionMId ModelNumber EmeiNo Color

c174f2c0-72f4-4746-b83d-21ffca6a5a61 620G 10001 grey

c174f2c0-72f4-4746-b83d-21ffca6a5a61 ONE 10005 black

table three

select TransactionMId,ModelNumber,EmeiNo,Color from tblTransactionS

TransactionMId ModelNumber EmeiNo Color

4fa11531-886a-4d15-af5c-7355cc3648f5 620G 10001 grey

now i want to show the data which is in table one and two but not in table three

Here is my Inner Join

SELECT TD.BrandId,TD.Color,TD.EmeiNo FROM dbo.tblTransactionD AS TD
INNER JOIN dbo.tblTransactionP AS TP
ON
TP.transactionMId=TD.transactionMId
INNER JOIN dbo.tblTransactionS AS TS
ON
TP.EmeiNo != TS.EmeiNo

but the executed result is showing data like this

BrandId Color   EmeiNo
1   grey    10001
1   black   10005

the record of 10001 should not be shown in the query because it is in table three

what is wrong in my inner join

Answer 1

If I understand your question correctly, you can try like this;

SELECT TD.BrandId,TD.Color,TD.EmeiNo FROM dbo.tblTransactionD AS TD
INNER JOIN dbo.tblTransactionP AS TP
ON TP.transactionMId=TD.transactionMId
where TP.EmeiNo not in (select distinct EmeiNo from dbo.tblTransactionS);

As your description tblTransactionS (three table) join is not necessary.

Answer 2

Following query will work:

SELECT TD.BrandId,TD.Color,TD.EmeiNo FROM dbo.tblTransactionD AS TD
INNER JOIN dbo.tblTransactionP AS TP
ON
TP.transactionMId=TD.transactionMId
where TP.EmeiNo not in (select distinct EmeiNo from dbo.tblTransactionS);