SQL Use Subquery results with NOT IN

I can imagine that question exists in some way, but I haven’t found what I was looking for.

I need to remove the found values from the result I’m getting in the main query.

Consider the following:

The mainquery:

    SELECT idTable
    FROM tblTables
    WHERE NOT IN idTables = ( **SUBQUERY HERE** )
          AND dtSeats >= 4
    LIMIT 1;

The subquery:

    SELECT idTable
        FROM tblTables,tblReservation
        WHERE tblTables.idTable = tblReservation.fiTable
        AND fiTime = 1
        AND dtResDate = "2020-06-16"

In the tblTables there are idTable and dtSeats.

In the tblReservation are fiTime and dtResDate.

The subquery can get up to three rows. I need to get the first free table with the lowest number of seats possible.

Thanks for helping me out!

Answer

Having the DDL and some sample data would be helpful, but I think what you are looking for is a NOT EXISTS clause. It returns everything in the outer query that doesn’t match with a record in the inner query.

SELECT idTable
FROM tblTables tt
WHERE NOT EXISTS (
    SELECT NULL FROM tblReservation tr WHERE tt.idTable = tr.idTable AND  
        tr.dtResDate = '2020-06-16'
)
AND dtSeats >= 4
ORDER BY tt.dtSeats
LIMIT 1