[prog] Sql query

Conor Daly conor.daly-linuxchix at cod.homelinux.org
Tue Jul 12 18:43:56 EST 2005


On Tue, Jul 12, 2005 at 09:33:43AM +0100 or so it is rumoured hereabouts, 
Conor Daly thought:
> On Tue, Jul 12, 2005 at 10:02:53AM +0200 or so it is rumoured hereabouts, 
> Tiera thought:
> > Hi!
> > 
> > I'm having some trouble coming up with a sql query that will work for the
> > following problem:
> > 
> > I have one table called product and one table called period.
> > 
> > product
> > -------
> > id
> > name
> > descr
> > 
> > period
> > ------
> > id
> > productid
> > status
> > startdate
> > enddate
> > 
> > A period is connected one product, but a product can have one or more
> > periods connected to it. A period always has a startdate, but doesn't need
> > to have a enddate. If there is multiple periods for a product only one of
> > them can be without a enddate (the last one), but it could also be that
> > all the periods connected to a product has enddates.
> > 
> > My problem is that I would like to get all the products and the status
> > from the last period for every product. I think I could solve this with a
> > subquery, but I'm using a mysql-server, version 3.23, so it doesn't have
> > subqueries yet.
> 
> Wild stab in the dark:
> 
> select a.id, a.name, a.descr, b.status from product a, period b
> where a.id=b.id
> and b.enddate is null;

Sorry, this should read 

where a.id=b.productid

And it doesn't address the case where all periods for a product have
enddates.  

Conor
-- 
Conor Daly <conor.daly at oceanfree.net>

Domestic Sysadmin :-)
---------------------
Hobbiton.cod.ie
 09:38:15  up 5 days, 12:32,  1 user,  load average: 0.05, 0.05, 0.01


More information about the Programming mailing list