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.