Derby database query

Question

I'm working on a calendar system. We are using Derby. I'm writing a script to ask (in my opinion) a very hard logic statement. I have been reading in derbys instructions but cant seem to find some instruction. I have used 'Antijoin' when I made the logic and it seems derby dose not support this. To my question. In the picture below. What would that statement be? It would suck to change the database tables because Derby dose not support 'Antijoin' because we have made so many other queries. Im sorry it is not written in English but the signs are international. I'm also comparing some variables from the script. The logic works as it is there it's just I am having a hard time to write it in Derby sql.

In the picture there are the relations. I don't have to use antijoin but cant get my head around another way to solve it.

EDIT: There was a question to not use the picture so i will explain. It's now in english so if you see different names on the pictures i translated it.

There are two tables involved.

Room

roomName numberOfPeople description

Meeting

(There is more here but i will only write what is important) roomName meetingStart meetingEnd

The query is to find witch rooms are available too use for the meeting.

What I have designed is that I will select from the meeting table only the meetings that are conflicting with the new reservation of the room your are trying to make. You cant do it the other way around in my opinion. It looks like this: reservationStart > meetingStart AND meetingEnd > reservationEnd

Then i will 'antijoin' with the lists of all the rooms. This will make a list of all the available rooms. The problem is that it seems Derby cant antijoin. Is there another way to construct a antijoin with other joins? Hope you understand what I'm writing

EDIT2:

I'mnot getting any error because I have not tried any queries. I have been working on making the query and what i got thus far is this:

SELECT roomName, numberOfPeople, description FROM ROOM WHERE roomName NOT IN (SELECT roomName, meetingStart, meetingEnd FROM Meeting WHERE reservationEnd > meetingStart AND meetingEnd > reservationStart);

But from what i have been reading there is no 'NOT IN' in Derby. How can I rephrase the query?

EDIT 3:

I have got the query. The error code I'm getting now is this:

ERROR 42X39: Subquery is only allowed to return a single column.

Answer 1

To find rooms busy with meetings,
SELECT roomNames from meeting ms, reservation rs where rs.reservationEnd > ms.meetingStart AND ms.meetingEnd > rs.reservationStart
now to find rooms available,you can antijoin with
SELECT roomNames from room.

Answer 2

You get `ERROR 42X39: Subquery is only allowed to return a single column.', because your subquery

SELECT roomName, meetingStart, meetingEnd FROM Meeting WHERE reservationEnd > meetingStart AND meetingEnd > reservationStart

returns 3 columns (roomName, meetingStart, meetingEnd).

In order for the NOT IN to work only 1 Columns is allowed. The Subquery will create a set of values, and for that to work only values from one column can be written to that set.

You need to change the subquery to:

SELECT roomName FROM Meeting WHERE reservationEnd > meetingStart AND meetingEnd > reservationStart

Answer 3

NOT IN works fine in Derby. Here is where you can find it in the docs: http://db.apache.org/derby/docs/10.7/ref/rrefsqlj23075.html#rrefsqlj23075

What is the actual query you are issuing, and the actual error you are receiving?