SQL Query to find duplicates in one table but only if at least one of the duplicates has an appointment which is in a different table

Thanks to this great community I’ve already had a huge success in my data searching. Using:

select patient.last, patient.first, patient.birth, count(*)
from patient
group by patient.last, patient.first, patient.birth
having count(*) > 1

It listed out all my duplicate values! Now, I’d like to take this a step farther so that I can list ONLY those duplicates that have an appointment on a certain day. There are several issues I’m running into though.

First only one of the two duplicates will have an appointment.
Second the appointment date is stored on a different table (fun enough called APPT)

I tried:

select appt.date
from appt
where appt.date=curdate()+1
union
select patient.last, patient.first, patient.birth, count(*)
from patient
group by patient.last, patient.first, patient.birth
having count(*) > 1

But this caused an error about columns not matching. I couldn’t figure out how to innerjoin because when I selected appt.date it affected the group by.

Any thoughts?

Answer

I assume (as you say in the comment) there is a column patunique in the table patient which is the primary key and also in the table appt which refernces the column in the table patient.
So use EXISTS twice to identify the rows that meet the conditions:

select p.last, p.first, p.birth, count(*) counter
from patient p
where 
  exists (select 1 from patient where patunique <> p.patunique and last = p.last and first = p.first and birth = p.birth)
  and 
  exists (select 1 from appt where patunique = p.patunique and date = curdate() + 1)
group by p.last, p.first, p.birth