Good day fellow programmers. I have 3 tables, with following sample records.
mem_id | mem_fname | mem_lname
1 | Ryan | Layos
2 | Dhave | Sebastian
3 | Staven | Siegal
4 | Ma Ethel | Yocop
5 | Kelvin | Salvador
6 | Herbert | Ares
status_id | mem_id | leader_id | process_id
1 | 2 | 1 | 2
2 | 3 | 5 | 3
3 | 4 | 6 | 4
4 | 5 | 1 | 4
5 | 1 | 6 | 4
process_id | process_type
1 | CONSOLIDATION
2 | PRE-ENCOUNTER
3 | ENCOUNTER
4 | POST-ENCOUNTER
mem_id | member_fname | member_lname | leader_fname | leader_lname | process_type
2 | dhave | sebastian | Ryan | Layos | PRE-ENCOUNTER
5 | Kelvin | Salvador | Ryan | Layos | POST-ENCOUNTER
SELECT member.mem_fname, member.mem_lname, leader.mem_fname, leader.mem_lname, tbl_process.process_type
tbl_member_status as mem_stats
tbl_members as member
tbl_members as leader
tbl_members ON mem_stats.member_id = member.mem_id
tbl_process ON tbl_process.process_id = mem_stats.process_id
leader.mem_fname = 'Ryan'
Because when you query. The number of rows in the result matters. Like: if your result is for fname = ryan but then the match for mem.id in table memberstatus is two and then in process table is again two. Inshort you will have 2 rows in final output. Can you try this :
Select M.member_fname, M.mem_lname P.process_type from tbl_members M, tbl_member_status MS, tbl_process P where M.mem_id = MS.mem_id and MS.process_id = P.process_id and where M.member_fname = 'ryan'
Okay i misunderstood your question at first. I have a solution for you which will improve your database schema. If one member has only one leader and a single leader has many memebers. Then why not create a different table called leader and connect to members table directly? So it will be a one to one relation. Which will make querying much simpler. So now you have 4 tables.
Select M.member_fname, M.mem_lname, L.fname, L.lname, P.process_type from tbl_members M, tbl_member_status MS, tbl_process P, tbl_leader L where M.leader_id = L.id and M.mem_id = MS.mem_id and MS.process_id = P.process_id and where M.member_fname = 'ryan'