Hiii ppl =] I want to ask a question of how to write a SQL statement.
The Scenario goes like this.
The Doctor can upload videos, and the patients that are assigned to him can see those videos. I have a ClinicianPatientAssignment table, where the columns are PatientID and ClinicianID. There are a number of clinicians, and each clinician with a number of patients. I also have a Users table, where all users are inside of it containing their UserID, name, contact number etc but i just need the userID from that table. On My Videos table, i have userID and videoID and other information. My original SQL statement was like this.
"SELECT A.*
FROM Videos A
INNER JOIN ClinicianPatientsAssignment B
ON A.UserID = B.ClinicianID
INNER JOIN Users C
ON B.PatientID = C.UserID
WHERE B.UserID = " +userID
"ORDER BY A.DateCreated DESC"
i got the video information according to the UserID in video table which is = to ClinicianID in ClinicianPatientsAssignment. then i got the userID from the aspx page and store it in B.UserID which is the PatientID in ClinicianPatientsAssignment. Am i doing this the correct way? And other thing is i need to know how to connect the patientID to ClinicianID like IF the Patient is under ClinicianID=3 then display all videos from clinicianID=3. Can anyone help me Fast D: