using LIKE with subqueries/JOIN 1

[patlv23]

Hi

Say I have these tables

Account
account_id char(9) ex. "ABC123456", "XYZ332211"
(ledger_id + sequence num)

Ledger
ledger_id char(3) ex. "ABC", "XYZ"
branch_id char(1) ex. "B", "C"

I'd like to get all accounts given a branch_id. This would be easy enough if account_id were broken into ledger_id and sequence num. I suppose it would look something like this:

SELECT *
FROM Account
JOIN Ledger
ON Account.ledger_id = Ledger.ledger_id
WHERE branch_id = 'B'

or

SELECT *
FROM Account
WHERE Account.ledger_id = (SELECT ledger_id
FROM Ledger
WHERE branch_id = 'B')

Could there be a LIKE version? something like:

SELECT *
FROM Account
JOIN Ledger
ON Account.account_id LIKE Ledger.ledger_id CONCAT '%'
WHERE branch_id = 'B'

or like:

SELECT *
FROM Account
WHERE account_id LIKE (SELECT ledger_id CONCAT '%'
FROM Ledger
WHERE branch_id = 'B')

Thanks in advance!

 

[BrianTyler]

It seems to me that you can do the join of Ledger_ID to left(Account_Id,3)

e.g.
Select .....
From Account,Ledger
where Ledger_id = left(account_id,3)
and branch_id = 'B'

Th main problem with this type of solution is that any index on Account_Id would probably not be used

 

[patlv23]

Thanks Brian! Just what I needed

 

[stevexff]

But really you should split account_id into ledger and id columns, as the existing column really contains two data items concatenated together...