Oracle's minus in MySQL?

[Anon]

Hi,

Is there any substitute for Oracle's 'minus' keyword in MySQL? I read in another post that you can do the following in Oracle:

select pid from players
minus
select pid from teams

The result: all pids that exist in players and do not exist in teams.

Is there a query I could use in MySQL to emulate this?
I really need to get this working, so any ideas would be greatly appreciated.

Thanks,
Louise

[[deleted]]

MySQL does not do that, but you can get the same effect by doing a LEFT JOIN and select all records where the entry from 'teams IS NULL'

SELECT pid
FROM players LEFT JOIN teams ON players.pid=teams.pid
WHERE teams.pid IS NULL

[Anon]

Thanks very much Vincent. That worked perfectly.

[Anon]

Thanks for the help! I am working on a guesthouse reservation system where there are 5 rooms: 2 double, 2 twin, and 1 family. To check availability I have to create a temporary table first of all bookings made on that date for that room type. Then I check for all room numbers that are of that type and select the rooms that are available. The queries are:

CREATE TEMPORARY TABLE booked
SELECT reservation.room_no AS booked FROM reservation, room WHERE room.room_no = reservation.room_no AND type = 'double' AND 20020201 BETWEEN arrival_date AND dept_date;

SELECT room_no FROM room LEFT JOIN booked ON room_no = booked WHERE booked IS NULL AND type = 'double';

This works perfectly - however, is there any way that I can do this in one select statement, as I have to give my script user 'create' and 'drop' permissions, which I would rather not do.

Also at the moment, it is only checking the first night, then I loop through the queries for each remaining night. Is there a way I can check availability for every night of the booking in the same query?
Thanks again,
Louise