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!

IBM DB2 v8.1.0.36
Windows 2000

SELECT *
FROM Account
JOIN Ledger ON SUBSTR(Account.account_id,1,3) = Ledger.ledger_id
WHERE branch_id = 'B'