List:General Discussion« Previous MessageNext Message »
From:RAPPAZ Francois Date:September 22 2005 5:55am
Subject:RE: SQL to use left join and find missing values
View as plain text  
 
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:brent@stripped] 
> Sent: mardi, 20. septembre 2005 21:29
> To: RAPPAZ Francois
> Cc: mysql@stripped
> 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? 
> > unsub=brent@stripped
> >
> >
> >
> 
> -- 
> Brent Baisley
> Systems Architect
> Landover Associates, Inc.
> Search & Advisory Services for Advanced Technology Environments
> p: 212.759.6400/800.759.0577
> 
> 
> 
Thread
SQL to use left join and find missing valuesRAPPAZ Francois20 Sep
  • Re: SQL to use left join and find missing valuesBrent Baisley20 Sep
RE: SQL to use left join and find missing valuesRAPPAZ Francois22 Sep