help on Joining 2 tables

vh3r

hello fellow programmers I badly needed help on this.

how can i join 2 tables.

tbl1

tblId
number
dateprocessed

tbl2

tblId
destNumber
serverdatetime

basically i can join them via number and destnumber but i need them be join via dateprocessed and serverdatetime by using substring(dateprocessed,1,16) and substring(serverdatetime,1,16)

because it always gives me an error everytime im using the substring on joining 2 tables.

thank you in advance.

Lars_Berg

Post the query you are using and the error message that you get.

vh3r

here's the sql query i made

select a.billed, b.type, substring(dateprocessed,1,16) as DP1, substring(serverdatetime,1,16) as DP2 from msg.tbl_out as a. dlr_result.report as b where a.mobile=b.destAddr and a.DP1=b.DP2;

the error
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '. dlr_result.report_smart as b where a.mobile=b.destAddr and a.DP1=b.DP2' at line 1

Lars_Berg

select a.billed, b.type, dateprocessed
 from msg.tbl_out as a, dlr_result.report as b 
where a.mobile=b.destAddr 
and substring(dateprocessed,1,16) = substring(serverdatetime,1,16)

You can't use correlation names in the where clause.

Sxooter

'. dlr_result.report_smart as b

You need to remove that space so it looks like this:

'.dlr_result.report_smart as b