Hi Thanks for the help. However, the query you suggest Select 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
Gives only one reccord, hence the sum is incorrect. Upon trying a little bit harder, I have found that Select sum(PlaceNo) from boxes inner join car using (boxID) left outer join load on car.Travel.ID=load.TravelID and car.BoxNo=Load.BoxNo Where car.TravelID=1 AND Load.BoxNo is null seems to give the needed result (I have to test this query some more). Greetings François > -----Original Message----- > From: Brent Baisley [mailto:[EMAIL PROTECTED] > Sent: mardi, 20. septembre 2005 21:29 > To: RAPPAZ Francois > Cc: mysql@lists.mysql.com > Subject: Re: SQL to use left join and find missing values > > 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 too, 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]
