mysql - java and sql showed #1241 - Operand should contain 1 column(s)

Question

SELECT * FROM rooms WHERE roomID NOT IN (SELECT * FROM rooms r,reservation e WHERE  r.roomID=e.roomID AND (checkout > ?) AND (check-in < ?));

this is the SQL string for my java program, I wanted it to filter the rooms which aren't booked during selected check-in and check-out date. but the command showed this: #1241 - Operand should contain 1 column(s). I wonder what's the problem of it.

question from:https://stackoverflow.com/questions/65935828/java-and-sql-showed-1241-operand-should-contain-1-columns

Answer 1

I wanted it to filter the rooms which aren't booked during selected check-in and check-out date. but the command showed this:

If you want to find available rooms, I would suggest not exists:

SELECT r.*
FROM rooms r
WHERE NOT EXISTS (SELECT 1
                  FROM reservation res
                  WHERE res.roomID = r.roomID AND
                        checkin < :period_end AND
                        checkout > :period_start
                 );

You can use ? for the parameters, of course. I used :period_start and :period_end so the query is clear on how the values are being used.