Joining MySQL Tables and Calculating Counts - Output in PHP -

i trying query mysql database of available computers in busy public computer lab. have 2 tables, computers , compuse. compuse table updated each time user logs on or signs off computer. when logofftime null, computer in use. in example, computers 1, 2, 3, , 8 in use. computers 4, 5, 6, 7, 9, 10 available.

|-----------------------|-----------------------|----------|-----------| |       logontime       |       logofftime      |   recid  | compname  | |-----------------------|-----------------------|----------|-----------| |  2011-05-13 13:45:16  |             <<null>>  |  310052  |  comp001  | |  2011-05-13 13:35:18  |  2011-05-13 13:39:37  |  310043  |  comp001  | |  2011-05-13 12:12:09  |  2011-05-13 12:33:37  |  309979  |  comp001  | |  2011-05-13 13:00:57  |             <<null>>  |  310018  |  comp002  | |  2011-05-13 11:30:13  |  2011-05-13 12:58:15  |  309940  |  comp002  | |  2011-05-13 09:36:15  |  2011-05-13 09:47:22  |  309850  |  comp002  | |  2011-05-13 09:25:29  |             <<null>>  |  309840  |  comp003  | |  2011-05-13 08:45:38  |  2011-05-13 09:24:03  |  309793  |  comp003  | |  2011-05-12 22:39:58  |  2011-05-13 00:36:31  |  309640  |  comp003  | |  2011-05-13 12:06:22  |  2011-05-13 12:50:23  |  309972  |  comp004  | |  2011-05-13 11:10:16  |  2011-05-13 12:01:16  |  309915  |  comp004  | |  2011-05-13 07:17:18  |  2011-05-13 09:42:10  |  309731  |  comp004  | |  2011-05-13 11:51:38  |  2011-05-13 12:15:35  |  309959  |  comp005  | |  2011-05-13 08:55:14  |  2011-05-13 09:47:48  |  309807  |  comp005  | |  2011-05-12 18:15:05  |  2011-05-12 18:15:16  |  309502  |  comp005  | |  2011-05-13 12:08:40  |  2011-05-13 13:16:41  |  309974  |  comp006  | |  2011-05-13 11:29:09  |  2011-05-13 12:05:56  |  309939  |  comp006  | |  2011-05-13 11:10:41  |  2011-05-13 11:19:14  |  309916  |  comp006  | |  2011-05-13 10:45:27  |  2011-05-13 11:16:44  |  309896  |  comp007  | |  2011-05-13 09:21:42  |  2011-05-13 09:55:48  |  309839  |  comp007  | |  2011-05-13 08:23:33  |  2011-05-13 09:14:24  |  309770  |  comp007  | |  2011-05-13 13:54:12  |             <<null>>  |  310058  |  comp008  | |  2011-05-13 13:38:53  |  2011-05-13 13:39:23  |  310045  |  comp008  | |  2011-05-13 10:13:23  |  2011-05-13 13:26:51  |  309878  |  comp008  | |  2011-05-13 12:16:06  |  2011-05-13 13:26:21  |  309984  |  comp009  | |  2011-05-13 10:13:09  |  2011-05-13 12:15:13  |  309877  |  comp009  | |  2011-05-13 08:23:22  |  2011-05-13 10:07:08  |  309769  |  comp009  | |  2011-05-13 13:45:51  |  2011-05-13 13:47:11  |  310053  |  comp010  | |  2011-05-13 11:18:12  |  2011-05-13 13:19:59  |  309925  |  comp010  | |  2011-05-13 07:28:50  |  2011-05-13 09:50:09  |  309737  |  comp010  | |-----------------------|-----------------------|----------|-----------| 

this data needs joined table indicates floor of building computer on. table looks similar this:

|--------|-----------|-------------| | compid | compname  |   location  | |--------|-----------|-------------| |    95  |  comp001  |  1st floor  | |    96  |  comp002  |  1st floor  | |    97  |  comp003  |  1st floor  | |    98  |  comp004  |  1st floor  | |    99  |  comp005  |  2nd floor  | |   100  |  comp006  |  2nd floor  | |   101  |  comp007  |  2nd floor  | |   102  |  comp008  |  3rd floor  | |   103  |  comp009  |  3rd floor  | |   104  |  comp010  |  3rd floor  | |--------|-----------|-------------| 

the first table, compuse, has several thousand records in used calculating usage statistics lab. need create output of how many computers available on each level. don't know how join location computers table compname compuse table without disrupting query. initially, ran following query determine total number of computers available, need able break down area of building.

select     (select count(compname)          compusage.computers) -      (select count(compname)          compusage.compuse          logofftime null) 

can me construct query output number of available computers on each level of building? expected result in example be:

level 1:  1 computer (of 4) available level 2:  3 computers (of 3) available level 3:  2 computers (of 3) available 

thanks, jordan

update: similar i'm trying do, can't figure out how adapt these queries: php::group , subtract 2 tables

update 2: trying adapt now, don't understand subqueries enough work:

select (totalcomps.total - inuse.inusecomps) available, totalcomps.total  (select count(compname) total, location           compuse.computers          group location) totalcomps  inner join (select count(compname) inusecomps               computers.compuse              logofftime null              group location) inuse  on computers.compname = compuse.compname 

update 3: i've updated sample data more of real-world example more records.

have tried this?

select location, count(1) numberofcomps  computers c    left join compuse cu on c.compname = cu.computer  logofftime null  group location