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:
Loading
Toh Zuan YiPosted Aug 17, 2011, 5:50 AM
SELECT A.*, C.*, B.UserID AS Expr1
FROM Videos AS A
INNER JOIN
ClinicianPatientsAssignment AS C
ON A.UserID = C.ClinicianID
INNER JOIN
Users AS B
ON C.PatientID = B.UserID
WHERE (B.UserID = @UserID)
ORDER BY A.DateCreated DESC
Amit ChoudharyPosted Aug 17, 2011, 5:40 AM
Rather than keeping Userid and Video ID in Videos table you can simply relate them based on Clinic ID. see the diagram below:
If this is the scenario you could easily achieve your reports by simple join queries.
Hope this was helpful.
Padma MathanPosted Aug 17, 2011, 5:15 AM