SQL Query: Using AND/OR in the WHERE clause

Question

I'm currently working on a project where I have a list of dental patients, and I'm supposed to be displaying all the patients who have two specific procedure codes attached to their profiles. These patients must have BOTH procedure codes, not one or the other. At first I thought I could accomplish this by using a basic AND statement in my WHERE clause, like so.

SELECT [stuff]
FROM [this table]
WHERE ProcedureCode = 'ABC123' AND ProcedureCode = 'DEF456';

The query of course returns nothing because the codes are entered individually and you can't have both Procedure 1 and Procedure 2 simultaneously.

I tried switching the "AND" to "OR" just out of curiosity. Of course I'm now getting results for patients who only have one code or the other, but the patients who have both are showing up too, and the codes are displayed as separate results, like so:

Patient ID       Last Name       First Name       Code       Visit Date

1111111          Doe             Jane             ABC123     11-21-2015
5555555          Smith           John             ABC123     12-08-2015
5555555          Smith           John             DEF456     12-08-2015

My SQL is pretty rusty these days. I'm trying to think of a way to filter out patients like Jane Doe and only include patients like John Smith who have both procedure codes. Ideas?

ADDING INFO BASED ON CHRISTIAN'S ANSWER:

This is what the updated query looks like:

SELECT PatientID, LastName, FirstName, Code, VisitDate
FROM VisitInfo
WHERE PatientID IN
(
SELECT PatientID
FROM VisitInfo
WHERE Code = 'ABC123' OR Code = 'DEF456'
GROUP BY PatientID
HAVING COUNT(*) > 1
)
AND (Code = 'ABC123' OR Code = 'DEF456');

So I'm still getting results like the following where a patient is only showing one procedure code but possibly multiple instances of it:

Patient ID       Last Name       First Name        Code       Visit Date 

1111111          Doe             Jane              ABC123     11-02-2015
1111111          Doe             Jane              ABC123     11-21-2015
5555555          Smith           John              ABC123     12-08-2015
5555555          Smith           John              DEF456     12-08-2015
5555555          Smith           John              ABC123     12-14-2015
9999999          Jones           Mike              DEF456     11-22-2015
9999999          Jones           Mike              DEF456     12-06-2015

Even though Jane Doe and Mike Jones have 2 results, they're both the same code, so we don't want to include them. And even though John Smith still has 2 of the same code, his results also include both codes, so we want to keep him and other patients like him.

ANOTHER UPDATE:

I just learned that I now need to include a few basic demographic details for the patients in question, so I've joined my VisitInfo table with a PatientInfo table. The updated query looks like this:

SELECT v.PatientID, v.LastName, v.FirstName, v.Code, v.VisitDate, p.DateOfBirth, p.Gender, p.PrimaryPhone
FROM VisitInfo v JOIN PatientInfo p ON v.PatientID = p.PatientID
WHERE v.PatientID IN
(
SELECT PatientID
FROM VisitInfo
WHERE Code = 'ABC123' OR Code = 'DEF456'
GROUP BY PatientID
HAVING COUNT(*) > 1
)
AND (Code = 'ABC123' OR Code = 'DEF456');

I wasn't sure if the new JOIN would affect anyone's answers...

Answer 1

There's a bunch of ways to skin this particular cat - here's another one:

WITH ABC123 AS (SELECT DISTINCT PATIENTID
                  FROM VISITINFO
                  WHERE PROCEDURECODE = 'ABC123'),
     DEF456 AS (SELECT DISTINCT PATIENTID
                  FROM VISITINFO
                  WHERE PROCEDURECODE = 'DEF456')
SELECT v.PATIENTID, v.LASTNAME, v.FIRSTNAME, v.PROCEDURECODE, v.VISITDATE,
       p.DateOfBirth, p.Gender, p.PrimaryPhone
  FROM VISITINFO v
  INNER JOIN ABC123 a
    ON a.PATIENTID = v.PATIENTID
  INNER JOIN DEF456 d
    ON d.PATIENTID = v.PATIENTID
  INNER JOIN PatientInfo p
    ON v.PatientID = p.PatientID
  WHERE v.PROCEDURE_CODE IN ('ABC123', 'DEF456')
  ORDER BY v.PATIENTID, v.VISITDATE, v.PROCEDURECODE;

What we're doing here is using a couple of CTE's to get the PATIENTID's for each patient who has the the procedures in question performed. We then start with all records in VISITINFO and inner join those with the two CTE's. Because an INNER JOIN requires that matching information exist in the tables on both sides of the join this has the effect of retaining only the visits which match the information in both of the CTE's, based on the join criteria which in this case is the PATIENTID.

Best of luck.

Answer 2

SELECT *
FROM TABLE
WHERE Patient_ID IN
(
SELECT Patient_ID
FROM TABLE
WHERE Code = 'ABC123' OR Code = 'DEF456'
GROUP BY Patient_ID
HAVING COUNT(*) = 2
)
AND (Code = 'ABC123' OR Code = 'DEF456')

UPDATE 1:

As a patient can have multiple ´procedure codes´, this way will work better:

SELECT *
FROM   TABLE T1
WHERE  EXISTS (SELECT 1 
               FROM   TABLE T2 
               WHERE  T1.Patient_ID = T2.Patient_ID 
               AND    T2.Code = 'ABC123')
AND    EXISTS (SELECT 1 
               FROM   TABLE T2 
               WHERE  T1.Patient_ID = T2.Patient_ID 
               AND    T2.Code = 'DEF456')
AND   T1.Code IN ('ABC123','DEF456')

Answer 3

Select the records with the two codes, but use COUNT OVER to count the distinct codes per patient. Only keep those records with a count of 2 codes for the patient, i.e. patients with both codes.

select patient_id, last_name, first_name, code, visit_date
from
(
  select mytable.*, count(distinct code) over (partition by patient_id) as cnt
  from mytable
  where code in ('ABC123','DEF456')
) data
where cnt = 2;