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?
> unsub=brent@stripped
>
>
>
--
Brent Baisley
Systems Architect
Landover Associates, Inc.
Search & Advisory Services for Advanced Technology Environments
p: 212.759.6400/800.759.0577
