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

Answers


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

Need Your Help


About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.