SQL remove results from list-CodePudding

I have a Locations table, and a Visit table. Each location need to be visited annually based on a particular visit type. When we log a Visit, we add the type of visit it is and for what location (date time and all that other stuff). I need to deduce from what we collect, those locations that have not been visited.

For example, Locations A-Z (26 locations) can have 3 types of visits (Meeting, Review and Annual).

Some locations might not get visited at all while others might get visited more than once.

I need to know which locations have not had an Annual visit.

The SQL Statement that i have seems to give me a list of locations that have had an annual visit but i dont see a way of either eliminating that one entry from the larger list OR possible filtering the large list .

SELECT
     Location.Name
    ,Location.ID
    ,Visit.Date
    ,Visit.Time
    ,Visit.Visit_Type
FROM Location
INNER JOIN Visit
    ON Location.ID = Visit.LocationID
WHERE Visit.Group_Type = 'School'
AND ( Visit.Date BETWEEN '2021' AND '2022' )

CodePudding user response：

One way is to check for the lack of an annual, visit using the EXISTS statement.

SELECT        
   L.Name, L.ID
FROM          
   Location L             
WHERE 
   NOT EXISTS
   (
       SELECT 
          * 
       FROM 
          Visit V2 
       WHERE 
          V2.LocationID = L.LocationID 
          AND V2.Group_Type = 'School' 
          AND ( V2.Date BETWEEN '2021' AND '2022' )
   )

CodePudding user response：

Schema (MySQL v8.0)

CREATE TABLE location (
  `name` VARCHAR(8),
  `id` INTEGER
);

INSERT INTO location
  (`name`, `id`)
VALUES
  ('here', '1'),
  ('there', '2'),
  ('anywhere', '3');

CREATE TABLE visit (
  `id` INTEGER,
  `type` VARCHAR(7)
);

INSERT INTO visit
  (`id`, `type`)
VALUES
  ('1', 'meeting'),
  ('1', 'review'),
  ('2', 'meeting'),
  ('2', 'annual'),
  ('3', 'annual');

No Annual Visit

SELECT name
FROM location
JOIN visit 
USING(id)
GROUP BY name
HAVING COUNT(CASE WHEN type = 'annual' THEN 1 END) = 0;

name
here

View on DB Fiddle

CodePudding user response：

You can get a list of all visits this year, then use an OUTER JOIN to find the locations that haven't been visited this year yet.

   ;WITH visitsThisYear AS
    (
        SELECT
        *
        FROM
          Visit
        WHERE 
            Visit.Group_Type = 'School'
            AND ( Visit.Date BETWEEN '2021' AND '2022' )
    ),        
    SELECT 
      Location.Name,
      Location.ID
    FROM
      LOCATION
        LEFT OUTER JOIN visitsThisYear
           ON Location.ID = visitsThisYear.LocationID
    WHERE Location.ID IS NULL