Select rows from table where a certain value in a joined table does not exist-CodePudding

I have two tables, playgrounds and maintenance, which are linked with a foreign key. Whenever there is a maintenance on a playground, it will be saved in the table and connected to the respective playground.

Table A (playgrounds): playground_number

Table B (maintenance): playground_number (foreign key), maintenance_type (3 different types), date

What I now want is to retrieve all the playgrounds on which a certain type of maintenance has NOT been performed yet IN a certain year. For instance all playgrounds that do not have a maintenance_type = 1 in the year 2022 connected yet, although there could be multiple other maintenance_types because they are more frequent.

This is what I have tried (pseudo):

SELECT DISTINCT A.playground_number
FROM table A
JOIN table B ON A.playground_number = B.playground_number (FK)
WHERE NOT EXISTS (SELECT B.maintenance_type FROM table B
WHERE B.maintenance_type = 1 AND year(B.date) = 2022

However this will return nothing as soon as there is only one entry with maintenance_type 1 within the table.

I am struggling with this query for a while, so would appreciate some thoughts :) Many thanks.

CodePudding user response：

You need to correlate the exists subquery to the outer B table. Also, you don't even need the join.

SELECT DISTINCT a.playground_number
FROM table_a a
WHERE NOT EXISTS (
    SELECT 1
    FROM table_b b
    WHERE b.playground_number = a.playground_number AND
          b.maintenance_type = 1 AND
          YEAR(b.date) = 2022
);

CodePudding user response：

Please consider this. I don't think you need JOIN.

SELECT DISTINCT A.playground_number
FROM table A
WHERE A.playground_number NOT IN (SELECT B.playground_number FROM table B
WHERE B.maintenance_type = 1 AND year(B.date) = 2022)

Please let me know if I understand it incorrectly.