If you want to join a table more than once, you need to refer to it
AS something else.
select sum(PlaceNo)
from boxes
inner join car using (boxID)
inner join load using (TravelID)
left outer join load AS Load2 on car.BoxNo=Load2.BoxNo
Where load.TravelID=1 AND Load2.BoxNo is null
On Sep 20, 2005, at 2:40 AM, RAPPAZ Francois wrote:
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/mysql?
[EMAIL PROTECTED]
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
