a little help with a SELECT statement..

Anon

Hopefully someone can shed some light on a problem I'm having

I have 2 tables: "game_sched" and "referees"

The Game_sched consists of several fields which include game_date, start_time, end_time and Referee1, Referee2, and Referee3.

The Referee fields corespond to the id numbers of the Referees in the referee table.

What I'm trying to do is, given a specific date and time, find a list of referees that do not apear in the game_sched for that give date and time.

Anon

I would probably do two separate queries.

Compile the list of referees that are scheduled for that given date and time.
Select all referees NOT IN the list from query number one.

Example:

Query 1:
SELECT Referee1, Referee2, Referee3 FROM game_sched WHERE game_date=2000-09-18 AND start_time > 20:00 AND end_time < 22:00;

Loop through the results and compile the list of referee's that are already scheduled.

Say the results you got are
$result=1,4,6,18,22,53

Query 2 will be
SELECT * from referees WHERE (referee_id NOT IN ($result));

My syntax may not be 100% correct but this should get you close.

Good Luck.

Anon

I should have mentioned that I'm using MySQL.

MySQl doesn't support NOT IN.

But I will play with the 2 select statement idea.

Thanks,

Paul

Anon

I don't think this is possible using only MySQL (at least if you don't want to use more than 5 queries 😉. But with a little help from PHP it shouldn't be that difficult.
Select all referees from the referee-table and put them in an Array. Next, select all the referees that appeared on that date/time from the Game_sched.

Use a simple for-loop to eliminate the referees that DID appear, and you should have a list of referees who DIDN'T.

Hope this helps (a bit).

Anon

I just tried a NOT IN query using MySQL 3.22.32 and it worked just fine.

Thought you might want to know.

Anon

Hmm, that is the version I'm using as well.

I'll try it again.

Anon

Thanks for the help so far the NOT IN looks to be working.

But I'm having trouble figuring out the right select statement to find all referee who would be refereeing during a certain time frame.

Anon

The easy part should be the matching date.
WHERE date_col=your_date

What format and variable type are you saving the time as? Hopefully you are saving it as a number using the 24 hour clock or as a time.

If so you can do a BETWEEN comparison.

Example
WHERE your_time BETWEEN time_value1 AND time_value2

So all together it should be something like this.
WHERE date_col=your_date AND your_time BETWEEN time_value1 AND time_value2

You might have to play with the parenthesis to make it work correctly.

Good Luck.

Anon

Ahh.. BETWEEN! I didn't think of that.

Thanks...

For those who've read this far here is what finally worked

1st I selected all referees who were assigned a game during the Time span and Date I was searching for.

"SELECT referee1,referee2,referee3 FROM referees WHERE game_date=$gdate AND ((game_start_time BETWEEN $gtime1 AND $gtime2) AND (game_end_time BETWEEN $gtime1 AND $gtime2))"

I dumped the result into a temporary table (templist)

Then to find all referees who haven't been assigned I used the following search

"SELECT R.* FROM referees AS R LEFT JOIN templist AS T ON R.id_ref=T.id_ref WHERE T.id_ref is NULL"

Thanks for all the input.

Paul