Is there a better way to do this WHERE?
My SQL is a little rusty and was just curious if there is a better way to do this WHERE clause? Assuming that there is a way, which I'm sure there is. It's runs pretty quick as it is, but I just don't like it and would like to know if it can be improved upon.
SELECT DISTINCT p.firstname , p.middlename , p.lastname , p.gender , p.dob , p.id AS patientid , pr.id AS practiceid , pr.[name] AS practicename, pr.parentaco , pp.encounterdate FROM ((aco.patients_practices patients_practices LEFT OUTER JOIN aco.patients p ON ( patients_practices.patientid = p.id ) ) LEFT OUTER JOIN aco.practices pr ON ( patients_practices.practiceid = pr.id ) ) INNER JOIN aco.patientpreferences pp ON ( pp.patientid = p.id ) WHERE ( pr.parentaco = (SELECT parentaco FROM aco.practices WHERE master_companyid = 763 ) OR pr.id = (SELECT parentaco FROM aco.practices WHERE master_companyid = 763 ) ) AND pp.encounterdate IS NOT NULL
You could fetch the value from you sub-query first and put it in variable.
DECLARE @P INT SELECT @P = parentaco FROM aco.practices WHERE master_companyid = 763
Use the variable in the query
WHERE (pr.parentaco = @P or pr.id = @P) AND pp.encounterdate IS NOT NULL