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.: 41 (0)26 300 92 60
Fax.: 41 (0)26 300 97 30
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
