[prog] Sql query

[Conor Daly]

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;

This assumes that only one endate is null per product. 

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

Domestic Sysadmin :-)
---------------------
Hobbiton.cod.ie
 09:31:26  up 5 days, 12:25,  1 user,  load average: 0.00, 0.01, 0.00