get the opposite results from a SELECT query

Question

these are my tables: ``` `room`(roomID,roomNum) `customer`(customerID,Surname,etc) `contract`(contractID,roomID,weekNum) `paymen`t(paymentID,customerID,contractID,YearKoino) ``` and when i use the following query: ``` `select` room.roomnum `from` payment,contract,room,customer `where` payment.contractID = contract.contractID `and` contract.roomID=room.roomID `and` customer.customerID=payment.customerID `and` contract.weeknum='40' `and` payment.YearKoino='2007' ; ``` the results i am getting are: ``` +---------+ | roomnum | +---------+ | Δ-12 | | Γ-22 | | Α-32 | | Γ-21 | | Δ-11 | | Ε-12 | | Γ-31 | | Ε-22 | | Α-22 | | Δ-12 | | Γ-12 | +---------+ 11 rows in set ``` what i want to do is to run a query that gives me the exact opposite results(roomnums in table room that are not in table payment).This can be done by comparing the roomum results from the above query with the column roomnum in the room table.some of my efforts so far : ``` `Select` room.roomnum `from` room `where` NOT EXISTS…

Accepted Answer

I am not sure why your `not in` didn't work.

This should work (using not in with table name aliases):

```
   Select r1.roomnum 
   from room AS r1
   where r1.roomnum NOT IN 
       (select r2.roomnum 
        from payment,contract,room as r2,customer 
        where payment.contractID = contract.contractID
        and contract.roomID=r2.roomID 
        and customer.customerID=payment.customerID 
        and contract.weeknum='40' 
        AND payment.YearKoino='2007');
```

get the opposite results from a SELECT query

Solution

Problem