CODEWITHSUNDEEP
sqlsql-serverjoin
Petrik
Petrik

Reputation: 827

SQL join on left 6 charactes

I have got table with CCnumber in format like '441231xxxxxxxxxx' and then table with all Card BIN number which are in format of '441231'

How can I join those two tables?

I have tried: 

SELECT CardType, COUNT(Transactions.cc) 

FROM CardBin CB
JOIN CreditCardLog CC ON LEFT(CCnumber.CC,6)=BinNumber.CardBin

GROUP BY CardType;

I am using MS SQL. Thanks alot.

Upvotes: 0

Views: 76

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270331

I think you just need to use table aliases correctly in your query:

SELECT CardType, COUNT(cc.Transactions) 
FROM CardBin CB JOIN 
     CreditCardLog cc
     ON LEFT(cc.CCnumber, 6) = cb.BinNumber
GROUP BY CardType;

The table alias goes before the column, in the reference.

Upvotes: 2

Related Questions