sqlquery issue in Inner join
Hi
i have an table Creditcard and Childcreditcard1,Childcreditcard2
Childcreditcard1
CreditcardID TranscationDatetime
22222132132 2010-04-11 12:36:10.210
22222132134 2011-04-11 12:36:10.210
12364132122 2019-04-11 12:36:10.210
45677132124 2011-04-11 12:36:10.210
45677132124 2012-04-11 12:36:10.210
Childcreditcard2
CreditcardID TranscationDatetime
22222132132 2010-04-11 12:36:10.210
22222732134 2011-04-11 12:36:10.210
12364132192 2019-04-11 12:36:10.210
MasterCreditcard
CreditcardID primaryCreditID
22222132132 22222132132
22222132134 22222132132
12364132122 12364132122
45677132124 45677132124
45677232124 45677232124
78567723212 78567723212
23677232124 23677232124
45678944343 45678944343
22222732134 22222732134
12364132192 12364132192
now from these three tables i need to get the creditcardID which did not match only from MasterCreditcard
what ever CreditcardID we have in Childcreditcard1, Childcreditcard2 tables will always be there in MasterCreditcard table
result should be table should like this
45677232124
78567723212
23677232124
45678944343
select distinct(cc.CreditcardID) from MasterCreditcard CC
inner JOIN Childcreditcard1 c1 ON CC.CreditcardID <>c1.CreditcardID
inner JOIN Childcreditcard2 c2 ON CC.CreditcardID <>c2.CreditcardID
i tried like this but thi gives result of all CreditcardID in MasterCreditcard table
please help me on this
Thanks
prince
April 11th, 2012 3:49pm
Why didn't you post this as a follow-up in your original topic? Now this is a pure TSQL question, which should be asked in the TSQL forum.
I'm still not sure what you're objective is.
This is quite a confusing sentence:
now from these three tables i need to get the creditcardID which did not match only from MasterCreditcard
what ever CreditcardID we have in Childcreditcard1, Childcreditcard2 tables will always be there in MasterCreditcard table
Try supplying us with sample data that is a bit easier to understand. For example: A, B and C, instead of 45677232124. Such long integers are hard to compare.MCTS, MCITP - Please mark posts as answered where appropriate.
Free Windows Admin Tool Kit Click here and download it now
April 11th, 2012 4:02pm
Hi, it could be done in many ways by the way, but since i'm not testing my query, so the easiest will be
SELECT mc.CreditcardID FROM MasterCreditcard mc
left JOIN Childcreditcard1 c1 ON c1.CreditcardID = mc.CreditcardID
LEFT JOIN Childcreditcard2 c2 ON c2.CreditcardID = mc.CreditcardID
WHERE c2.CreditcardID IS NULL AND c1.CreditcardID IS null
Please mark ANSWERED if my reply contributed to your solution.
April 11th, 2012 4:06pm