This Image contains Person and Country table I want to connect based on Phone number starting three characters and country code of country table
I Wrote this query for connecting these two table
select *
FROM person
left join country on country_code=substring(phone_number,1,3)
Doubt-----
output for query by jarlh
Your main problem is that you are trying to equate 2 character codes like 51
with a 3 character codes like 051
I would pick out the first 3 characters of the phone number with the substring() and convert them to an integer to do the comparison like this, then the leading zero in one side of the test will be dropped
Also as you have a column called name
in both tables, I would
pick out the actual columns I want in the result specifically and alias them
select p.name as persons_name, c.name as country_name, c.country_code, p.phone_number
FROM person p
left join country c on c.country_code = cast(substring(p.phone_number,1,3) AS UNSIGNED);
Moncef Morocco 212 212-1234567
Maroua Morocco 212 212-6543214
Jonathan Peru 51 051-1234567
Meir Israel 972 972-1234567
Rachael Israel 972 972-0011100