CODEWITHSUNDEEP
phpmysql
Muhammad Rizwan
Muhammad Rizwan

Reputation: 488

MYSql left join two tables with column different values

I have two tables
1: tbl_member_registration with primary key (mem_id)
2: tbl_phone with foreign key (parent_code)

I want to left join these two tables but tbl_phone parent_code column has different values starting with prefix mem_ , how to join these two tables?

enter image description here

enter image description here

Upvotes: 0

Views: 1461

Answers (4)

Mohd Shafiq
Mohd Shafiq

Reputation: 27

select tbl_phone.*, tbl_member_registration.*
from tbl_member_registration.mem_id 
left join tbl_phone on tbl_member_registration.mem_id = (select substring(tbl_phone.parent_code, 5,2) );

Upvotes: 0

Hari17
Hari17

Reputation: 489

I hope this will help you,its working..

SELECT *
    FROM tbl_member_registration t1
    INNER JOIN tbl_phone t2
        ON t1.mem_id = SUBSTR(t2.parent_code,5, 2);

http://rextester.com/CSBZ60078

Upvotes: 0

always-a-learner
always-a-learner

Reputation: 3794

I haven't checked it but you can try this will work.

select *
from tbl_member_registration tmr join tbl_phone tp
on tp.parent_code = concat('mem_',tmr.mem_id)

For more reference detail: CONCAT

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

You may try the following join:

SELECT *
FROM tbl_member_registration t1
LEFT JOIN tbl_phone t2
    ON t1.mem_id = CAST(SUBSTRING_INDEX(t2.parent_code, '_', -1) AS SIGNED);

Demo

Going forward, you may want to clean up your tables such that the join condition can be had using equality =. There is no chance of an index being used on the parent_code column when it is being wrapped in an ugly call to SUBSTRING_INDEX.

Upvotes: 2

Related Questions