Query SQL JOIN and substring

Databases
1

Hello everyone, I need your help.

I have this tables in my db mysql:

tbl_A


idDTES	DATA_E		L_MT
DM20	2010-05-28	DM2040403-DANIS
DO60	2010-05-28	DO6015020-CIFERI
DM20	2010-05-28	DM2040414-VIANINI
DS10	2010-05-28	DS1035301-NARBOLIA
DM70	2010-05-28	DM7046212-ALENTO

tbl_b


idDTES	DATA_E		idL		Val
DI10	2010-05-28	DI1018614	1
DO40	2010-05-28	DO4072012	0
DM70	2010-05-28	DM7046212	0
DO60	2010-05-28	DO6006190	0
DM70	2010-05-28	DM7043013	0

And this JOIN query:


SELECT A.DATA_E
     , A.IDDTES
     , A.L_MT
     , CA.DATA_E
     , CA.IDDTES
     , CA.IDL
     , CA.VAL
FROM tbl_A A INNER JOIN 
     tbl_B CA ON CA.IDL = A.L_MT 
WHERE 1 
     AND CA.VAL = 0 
     AND CA.DATA_E = '2010-05-28'

I need this output but this query not working, I think for the different string value in the fields A.L_MT = DM7046212-ALENTO and CA.IDL = DM7046212:


idDTES	DATA_E		L_MT			idL		Val
DM70	2010-05-28	DM7046212-ALENTO	DM7046212	0

Any help would be very much appreciated and many thanks to any who can help me.

Thanks in advance.
Chevy

2

Thanks x your help, now this query extracting my output: :slight_smile:

SELECT A.DATA_E
     , A.IDDTES
     , A.L_MT
     , CA.DATA_E
     , CA.IDDTES
     , CA.IDL
     , CA.VAL
FROM tbl_A A INNER JOIN 
     tbl_B CA ON CA.IDL = LEFT(A.L_MT,9) 
WHERE 1 
     AND CA.VAL = 0 
     AND CA.DATA_E = '2010-05-28'
3

use CA.IDL = LEFT(A.L_MT,9)