Query Help 1

[heydyrtt]

Hello,

Hoping this can be done,
Have two tables facilities and payment_detail

Facilites table has a column facility_code, which is 3 characters like MDN.
Payment_Detail has a column account_number which contains the facility code and account number, it would look like MDN123456Booker/Tim. There are no relations between the two tables, and it's not possible to add a relation between them because the data in the payment_detail come's from a Bank and this all the information they have. So, the question is, is it possible to trim the account_number to MDN and then use it in the where clause for facility code. So I could use
Where fc.facility_code = pd.account_number

Thanks





Heydyrtt

 

[carp]

Yes - try substr(account_number, 1,3); for the example given, this will give you 'MDN'.

So your query structure would look something like

SELECT .....
FROM facilities f, payment_datail p
WHERE SUBSTR(p.account_number, 1,3) = f.facility_code;