SQL join To Fetch Mutiple Records-CodePudding

I need to find the firstName and Email address from of all teachers and parents in a single query

Here is the structure of the table :

// Patients Table
ID        guid             parentID     PatientName
1          234               1258      John
2          xyz               111       Paul


// Patients_teacher table
ID          PatiendGuid      teacherid    
1                122            132
2                xyz            1424
3                245            1545
4                xyz            1222

// Members table 
ID                 guid        email                   fname
22                 123        [email protected]            hello
111                xyz        [email protected]      parentName
1424               343        [email protected]     teacherName
1222               546        [email protected]    teacher2Name

And Here is the required Result:

//Required Result

fname        Email
parentName   [email protected]
techerName   [email protected]
teacher2Name [email protected]

The problem is when I tried to search using join I found a single row that contains parentID and TeacherID

Here is what I tried:

select Members.email,Members.fname
from Members
    join Patients on Members.guid = Patients.guid
    join Patient_Teacher on Patient_Teacher.patientguid = Patients.guid
where patients.guid = 'xyz'

CodePudding user response：

Here is the Solution :

select  Members.id, Members.email, Members.fname
from Patients
join Patient_Teacher on Patient_Teacher.patientguid = Patients.guid
join Members on (Patient_Teacher.teacherid = Members.id 
or Patients.parent = Members.id)

where patients.guid = 'xyz'

CodePudding user response：

Have you checked the following reasons?

1- You have a table named 'Patients_teacher' but in your solution, you're referring to it as 'Patient_teacher'.

2- In 'Patients_teacher' table, you have a column named 'patiendguid' but in your solution you're referring to it as 'patientguid'.