Re: Urgent help

Hint: selfjoin the table 3 times.

"Alan" <alanpltse_at_yahoo.com.au> wrote in message news:3f1f1b9a_at_news.comindico.com.au...
> I have the following table BucketTable :
>
> BucketID Time WaterLevel
> ------------------------------------------
> 1 9:00 Empty
> 1 10:00 Half
> 1 11:00 Full
> 2 9:20 Empty
> 2 11:30 Half
> 2 13:00 Full
> 3 11:00 Empty
> 3 14:10 Half
> 3 16:00 Full
>
> This table keeps the timestamp of the water level of the buckets.
> I want to write a SQL statement to show the following :
>
> BucketID Empty Half Full
> 1 9:00 10:00 11:00
> 2 9:20 11:30 13:00
> 3 11:00 14:10 16:00
>
> I tried
>
> Select BucketID,
> IF(WaterLevel='Empty', Time, ''),
> IF(WaterLevel='Half', Time, '')
> IF(WaterLevel='Full', Time, '')
> FROM Bucket
> ORDER BY BucketID;
>
> However, it did not work as it gave me 9 records rather than 3.
> I also tried DISTINCT and GROUP BY, it gave me 3 records but only with the
> Empty column has time.
>
>
Received on Thu Jul 24 2003 - 20:04:18 CEST