SQL to use left join and find missing values

[RAPPAZ Francois]

Hi all
with Mysql 3.23 I would like to find missing values in the following situation:

1) table boxes: describes kind of boxes differing by how many items I can put in.

Primary key is BoxID

boxID +  Places
A     +   1
B     +   3
C     +   5

2) table car describes how I can put some boxes on a car for a journey

Primary Key is composed of TravelID, BoxNo 
BoxNo is auto_increment, and start at 1 for each new travel

TravelID + BoxID + BoxNo
   1     +   A   +  1
   1     +   A   +  2
   1     +   B   +  3
   2	   +   A   +  1
   2     +   B   +  2
   2     +   C   +  3


3) table load describes how items can be placed in the boxes once a car has been monted
with boxes

Primary Key is composed of TravelID, BoxNo, PlaceNo
PlaceNo is auto_increment, and start at 1 for each new Box beeing load (it's just here to
make a primary key)

TravelID + BoxNo + PlaceNo + ItemName
    1    +   1   +   1     +   itemName1
    2    +   2   +   1     +   itemName2
    2    +   2   +   2     +   itemName3


I want to make a query to get how much free places there is for a given travel (ie how
much places have been prepared in table car that are not occupied in table load)

I would like to get this kind of query working

select sum(PlaceNo)
from boxes
inner join car using (boxID)
inner join load using (TravelID)
left outer join load on car.BoxNo=load.BoxNo
Where load.TravelID=1 AND load.BoxNo is null

but this query does not work because I have a duplicate table name (load). 
Left join would be possible if BoxNo was unique for the whole table load, but is there a
way to use left join here ?

This query works when there is one record in load for a given travel

select sum(PlaceNo)
from boxes
inner join car using (boxID)
where car.TravelID=1 AND car.BoxNo not in (select BoxNo 
	from load 
	where load.TravelID=1)

but when the subquery return null, the main query return null to when it should return the
total of free places...
How could I correct this ?

Thanks in advance

François

François Rappaz

Centre de documentation de la Faculté des Sciences 
Université de Fribourg
DokPe - Dokumentationszentrum der Naturwissenschaftlichen Fakultät Universität 
Freiburg 
Pérolles CH-1700 Fribourg Switzerland
http://www.unifr.ch/dokpe/
Tel.: [phone] 
Fax.: [phone]