MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


CREATE TABLE new_staff (`staff_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT, `firstName` VARCHAR(50) NOT NULL , `Mobile` VARCHAR(50) NOT NULL , `userType` VARCHAR(50) NOT NULL);

insert into new_staff (staff_id, firstName, Mobile, userType) values (1, 'Soupranjali', '9986125566', 'Teacher');
insert into new_staff (staff_id, firstName, Mobile, userType) values (2, 'Sujata', '8553880306', 'Teacher');
select * from new_staff;


staff_id
firstName
Mobile
userType


1
Soupranjali
9986125566
Teacher

2
Sujata
8553880306
Teacher


CREATE TABLE `new_student`
( `student_id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT , 
`first_Name` VARCHAR(50) NOT NULL ,
`fatherMobile` VARCHAR(50) NOT NULL , 
`user_type` VARCHAR(50) NOT NULL);
INSERT INTO new_student values(1, 'janarthan', '8553880306', 'Student');
INSERT INTO new_student values(2, 'Santanu', '8277904354', 'Student');
INSERT INTO new_student values(3, 'Sarvan', '8553880306', 'Student');
select * from new_student


student_id
first_Name
fatherMobile
user_type


1
janarthan
8553880306
Student

2
Santanu
8277904354
Student

3
Sarvan
8553880306
Student


select
ns1.student_id, ns1.first_name as student_first_name, 
ns1.fatherMobile, ns1.user_type as student_user_type,

ns2.firstName as staff_first_name, ns2.Mobile as staff_mobile, 
ns2.userType as staff_user_type

from new_student as ns1 
left join new_staff as ns2 on ns2.Mobile = ns1.fatherMobile;


student_id
student_first_name
fatherMobile
student_user_type
staff_first_name
staff_mobile
staff_user_type


1
janarthan
8553880306
Student
Sujata
8553880306
Teacher

2
Santanu
8277904354
Student
null
null
null

3
Sarvan
8553880306
Student
Sujata
8553880306
Teacher

student_id	first_Name	fatherMobile	user_type
1	janarthan	8553880306	Student
2	Santanu	8277904354	Student
3	Sarvan	8553880306	Student