Join values of two tables that represent the missing values between those tables in SQLITE-CodePudding

I have the following table

CREATE TABLE holes (`tournament_id` INTEGER, `year` INTEGER, `course_id` INTEGER, `round` INTEGER, `hole` INTEGER, `front` INTEGER, `side` INTEGER, `region` INTEGER);

With the following data sample

INSERT INTO holes (`tournament_id`, `year`, `course_id`, `round`, `hole`, `front`, `side`, `region`) VALUES
  ('33', '2016', '895', '1', '1', '12', '5', 'L'),
  ('33', '2016', '895', '1', '2', '18', '10', 'R'),
  ('33', '2016', '895', '1', '3', '15', '7', 'R'),
  ('33', '2016', '895', '1', '4', '11', '7', 'R'),
  ('33', '2016', '895', '1', '5', '18', '7', 'L'),
  ('33', '2016', '895', '1', '6', '28', '5', 'L'),
  ('33', '2016', '895', '1', '7', '21', '12', 'R'));

In addition, I have another table tournaments

CREATE TABLE tournaments (`tournament_id` INTEGER, `year` INTEGER, `R1` INTEGER, `R2` INTEGER, `R3` INTEGER, `R4` INTEGER);

With data

INSERT INTO tournaments VALUES
  (33, 2016, 715, 715, 895, 400);

The values for R1, R2, R3 and R4 present ids of the courses. I want the columns tournament_id, year and course_id that are missing in table holes based on all the possible values of table tournaments. With the help of this answer I tried the following:

WITH h AS (
SELECT DISTINCT tournament_id, year, course_id
FROM holes)

SELECT t.tournament_id, t.year
FROM tournaments t
WHERE NOT EXISTS (
  SELECT *
  FROM h
  WHERE h.tournament_id = t.tournament_id
  AND h.year = t.year
  AND h.course_id IN (t.R1, t.R2, t.R3, t.R4)
);

demo

The above goes a long way but I also want the h.course_id that is/are missing. Desired result:

33  2016  715
33  2016  400

These combinations of tournament_id, year and course_id are not present in holes. However, they do exists because they are present in tournaments.

CodePudding user response：

For this requirement you need a resultset consisting of all the values of the Rx columns which you can get with UNION in a CTE.
Then you can use NOT EXISTS to get all the combinations of id, year and course that do not exist in holes:

WITH cte AS (
  SELECT id, year, R1 AS course FROM tournaments 
  UNION 
  SELECT id, year, R2 FROM tournaments 
  UNION 
  SELECT id, year, R3 FROM tournaments 
  UNION 
  SELECT id, year, R4 FROM tournaments
)
SELECT c.*
FROM cte c
WHERE NOT EXISTS (
  SELECT *
  FROM holes h
  WHERE (h.id, h.year, h.course) = (c.id, c.year, c.course)
);

See the demo.