comparing 2 SQL queries

thelad

Howdy.

This is extremely urgentso if anyone can help.

I have one table consisting of employee details, which contains all their essential details.

I have a second table to list all of the employees on duty, whose primary key is the employee_number.
If a employee is on duty, his/her Id will appear on both tables.

My problem is that I wish to list out all the emnployees, both onduty and offduty,
and place and \"ON\" beside his/her name on the web-page, if on duty.

like this

while ($myrow = mysql_fetch_row($result)) {

printf(\"<tr><td><center><a href=search2.php3?num=%s>%s %s</td></center></a>\",$myrow[0],$myrow[1],$myrow[2]);

//I wish to put the code for the \"ON\" here

}

if anyone could help.
Thanks

Anon

Can't you then take the employee number returned from the first query, and use it to look up the onduty table, and if it's found, print ON, if not (ie it returns no records), it's OFF duty

Hope that helps,

Danny

kparker

Loops with nested queries are a way of life with mysql. However, if you can tolerate having the listing show up in employee id order, you can accomplish it with a single query on each table, run simultaneously.

while there are more on-duty employees
  while the next employee id is < the on-duty employee's id
    display the employee as off-duty
  display the on-duty employee

While there are more employees left in the master list
  display the employee as off duty

It's kind of like mergesort, except of course one list should always be ahead of the other. If you don't, it means you have found an on-duty employee whose ID is not listed in the master table. Hmm, alien infiltrator? :-)

Anon

If it's just a flat one-2-one relationship, can't you just add a "duty" field to the first table instead, and have a value ON or OFF accordingly? If not, you obviously can't! (I guess you'd already know that anyway, but a suggestion just in case!)