CODEWITHSUNDEEP
mysqlsqlselect
Huss
Huss

Reputation: 3

Mysql selecting from 2 tables while excluding the whole row if specific data was found

i have 2 tables that contains the following:

Table 1: patient (Contains almost all info i need) Table 2: ProcedureLog (Contains the procedures that was done to patients listed in Table 1)

I want to view all patients that have an outstanding balance but i want also to exclude the patients from this list that still have an outstanding balance BUT their procedure log contains specific codes that would identify them as orthodontics cases. i don't any orthodontics case in my report.

in Table 1, there is this info:

Patient Name | Patient Number | Estimated Balance
Jon          |    5           |      120
Sara         |    19          |      500

in Table 2:

Patient Number | Procedure CodeNum
5              |         222
5              |         223
5              |         244
5              |         80*****
19             |         80
19             |         10

if i run my query and exclude the value CodeNum that are between 222 and 244 i would still get Patient Jon in the resulted query because he had procedure no 80 which is common. How do i do exclude jon? 

SELECT distinct patient.Fname,patient.Lname,patient.PatNum,patient.EstBalance 
FROM opendental.patient,opendental.procedurelog 
where EstBalance>=1 
and patient.PatNum = procedurelog.PatNum 
and procedurelog.CodeNum not between 222 and 244;

I use distinct because i don't care about the values in table 2, table 2 only helps me identify the list of procedures that patient did.

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If I understand correctly, you want NOT EXISTS rather than a JOIN:

SELECT p.Fname, p.Lname, p.PatNum, p.EstBalance 
FROM opendental.patient p
WHERE EstBalance >= 1 AND
      NOT EXISTS (SELECT 1
                  FROM opendental.procedurelog pl
                  WHERE pl.PatNum = p.PatNum AND
                        pl.CodeNum not between 222 and 244
                 );

Upvotes: 1

Related Questions