join on multiple criteria

I'm using linq-to-sql to create a join between a table of prescriptions that's in the db and a list of patient call PatientList.

Let's say that the table and the list contain 开发者_StackOverflowan int called PatientID that I'll be using to create the join to filter the patient list by past prescription status.

I'm having a challenge with the where clause. The status of a prescription ranges from 1 to 6. There can be many different prescriptions per patient. I'm looking to remove from PatientList the patients that have had prescription with certain statuses. I want all the patients that have had at least one prescription with status 5, but never status 4 and 6, while statuses 1,2,3 are ok to have had. So for instance patients with prescritions a) 3,1,5,3,2 or b) 3,5,5,1,3 are ok but c) 2,1,5,6,2 or d) 1,3,4,2,1 are not ok because the first one contains a 6 and the second one doesn't have a 5.

This is what I have so far:

var TheOutput = from patients in PatientList
                join prescrip in MyDataContext.Prescriptions on 
                patients.PatientID equals prescrip.PatientID
                where prescrip.PrescripStatus == 5 && 

I'm stuck because if I do something like that, I'll have case c) turn out ok.

Thanks for your suggestions on this query problem.

So, you want all patients that have had a 5, but not a 4 or 6.

I'm not sure need the join. You just want to return patients, right?

I would try something like this:

var TheOutput = (from patients in PatientList
                 where (from prescrips in MyDataContext.Prescriptions
                        where prescrips.PatientID = patients.PatientID
                          && prescrips.PrescripStatus == 5
                        select prescrips).Any()
                   &&!(from prescrips in MyDataContext.Prescriptions
                        where prescrips.PatientID = patients.PatientID
                          && (prescrips.PrescripStatus == 4 || prescrips.PrescripStatus == 6)
                        select prescrips).Any()

                 select patients);

try something like this

var TheOutput = from patients in PatientList                 
                join prescrip in MyDataContext.Prescriptions on                  
                patients.PatientID equals prescrip.PatientID 
                join patients2 in PatientList on 
                patients.PatientID equals patients2.PatientID
                join prescrip2 in MyDataContext.Prescriptions on                  
                patients2.PatientID equals prescrip2.PatientID 
                where (prescrip.PrescripStatus == 5 && (prescrip2.PrescripStatus != 4 && prescrip2.PrescripStatus != 6))