CODEWITHSUNDEEP
sql
Gradatc
Gradatc

Reputation: 125

Select records that don't exist in a union between a table and a subset of that table

I have a table with appointments, past, present and future. I would like to be able to run a single query that would give me a list of appointments from a given date, with a status of "no show" that DO NOT have an appointment in the table with a date in the future.

So, what I have so far is (pseduocodey) 

SELECT *
FROM (SELECT *
    FROM Appointments
    WHERE Appointments.Date >= Today's Date)
WHERE NOT EXISTS
    (SELECT *
    FROM Appointments
    WHERE Appointments.PatID = SUBQUERYRESULTS.PatID)

The subquery would be

SELECT *
FROM Appointments
WHERE (Appointments.Status = "NoShow" AND (Appointment.Date is >= Start_date and <= End_date))

I'm not sure how to include the subquery to get it to work. I'm new to this, so please excuse the idiocy. Thank you.

Upvotes: 1

Views: 50

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You seem to want not exists as a where condition. Based on your description, this seems to be:

select a.*
from appointments a
where a.status = 'no show' and
      a.date = @date and
      not exists (select 1
                  from appointments a2
                  where a2.patid = a.patid and a2.date > current_ate
                 );

If the date column has a time component, then the date comparison needs to take this into account.

Upvotes: 2

Lee Mac
Lee Mac

Reputation: 16015

Here's another option (albeit untested) which uses a LEFT JOIN in place of the subquery:

SELECT t.*
FROM 
    Appointments t LEFT JOIN Appointments u 
    ON t.PatID = u.PatID AND t.Date < u.Date
WHERE
    t.Status = "NoShow"  AND
    t.Date >= Start_date AND 
    t.Date <= End_date   AND
    u.PatID IS NULL

The line u.PatID IS NULL essentially performs the selection of those records with no future appointment.

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123484

appointments ... with a status of "no show" that DO NOT have an appointment in the table with a date in the future

This seems to work (tested with Access 2010), and includes "Start_date" and "End_date" comparisons to limit the 'NoShow' appointments to a date range:

SELECT a1.*
FROM Appointments a1
WHERE a1.Status='NoShow'
    AND a1.Date >= Start_date AND a1.Date <= End_date
    AND NOT EXISTS
    (
        SELECT * 
        FROM Appointments a2 
        WHERE a2.PatID = a1.PatID
            AND a2.Date > a1.Date
    )

Upvotes: 0

Related Questions