Find missing rows in one table based on another table in Sqite3-CodePudding

I have two tables, tournaments and pinsheets. The tables looks as follows:

CREATE TABLE "pinsheets" (
    "tournament"    INTEGER,
    "year"  INTEGER,
    "course"    INTEGER,
    "round" INTEGER,
    "hole"  INTEGER,
    "front" INTEGER,
    "side"  INTEGER,
    "region"    INTEGER
);

Sample of data:

2   2015    6   1   1   18      C
2   2015    6   1   2   8   4   L
2   2015    6   1   3   22      C
2   2015    6   1   4   45  4   R
2   2015    6   1   5   26  6   L

and

CREATE TABLE "tournaments" (
    "tournament"    INTEGER,
    "year"  INTEGER
);

Sample:

Table tournaments contains of all the theoretical possible data. The actual collected information is saved in pinsheets. I want to loop over tournaments and see which observations I am missing. Basically the method should be as follows:

get tournament/year combination
check for round {1, 2, 3, 4} whether the tournament/year/round combination exists in pinsheets

My unsuccessful attempt:

  SELECT *
  FROM tournaments t
  WHERE NOT EXISTS (
    SELECT *
    FROM pinsheets pin
    WHERE  t.tournament = pin.tournament
    AND t.year = pin.year 
    AND (pin.round = 1 
            OR pin.round = 2
            OR pin.round = 3
            OR pin.round = 4)
            )

Desired output:

tournament   year   round
2   2015    3
2   2016    2
2   2017    2

CodePudding user response：

You need the CROSS join of tournaments to all the possible values of rounds (which can be obtained with a CTE):

WITH rounds(round) AS (VALUES (1), (2), (3), (4))
SELECT t.tournament, t.year, r.round
FROM tournaments t CROSS JOIN rounds r
WHERE NOT EXISTS (
  SELECT *
  FROM pinsheets p
  WHERE (p.tournament, p.year, p.round) = (t.tournament, t.year, r.round)
);

Or, with a LEFT join to pinsheets:

WITH rounds(round) AS (VALUES (1), (2), (3), (4))
SELECT t.tournament, t.year, r.round
FROM tournaments t CROSS JOIN rounds r
LEFT JOIN pinsheets p
ON (p.tournament, p.year, p.round) = (t.tournament, t.year, r.round)
WHERE p.tournament IS NULL;

See the demo.