Need help composing a query looking for missing data
I have a sql table that contains a patient id column, an assignment type column and an assignment id column. a patient can have multiple assignments of different and same types. I need to come up with a query to search the table of any patients that do not have a certain assignment type assigned to them. For example an assignment type of 41 is a doctor, if no doctor has been assigned the row doesn't exist though. So patient A might have assignments types 38, 32, 4 and so on but if no doctor then the row for 41 does not even exist in the table. I need to find patient ID's that do not have that row.
It's Win Server 2008 R2 and sql 2008.
It's Win Server 2008 R2 and sql 2008.
ASKER
I wrote it out like this:
SELECT *
FROM PT_BASIC AS P
LEFT OUTER JOIN PT_ASSIGNMENT AS A
ON P.PATIENT_ID = A.PATIENT_ID
AND A.ASSIGNMENT_ID = '41'
WHERE A.ASSIGNMENT_ID IS NULL
That returns all patients. Can I use the "is null" at the end when the row will not exist is there is no 41?
SELECT *
FROM PT_BASIC AS P
LEFT OUTER JOIN PT_ASSIGNMENT AS A
ON P.PATIENT_ID = A.PATIENT_ID
AND A.ASSIGNMENT_ID = '41'
WHERE A.ASSIGNMENT_ID IS NULL
That returns all patients. Can I use the "is null" at the end when the row will not exist is there is no 41?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That may be gettign us closer. It returns patients that have a type 41 assigned to them, BUT the number of patients the 2nd query returned is about 266 patients less than what the first query gave us. I am thinking that those are the records we need, just need to know how to show those versus the ones we are getting
Is ASSIGNMENT_ID numeric (integer or similar) or char/varchar?
The query I wrote can't return a patient that has an assignment id equal to '41'.
The query I wrote can't return a patient that has an assignment id equal to '41'.
ASKER
Scott, that was it. I had the column name wrong in your query and now I have all the patients. Thank you very much!!
SELECT P.*
FROM Patient AS P
LEFT OUTER JOIN Assignment AS A
ON P.PatientID = A.PatientID
AND A.AssignmentTypeID = 41
WHERE A.AssignmentID IS NULL