Re: Urgent help
Date: 28 Jul 2003 03:07:08 -0700
"Alan" <alanpltse_at_yahoo.com.au> wrote in message news:<3f24c465_at_news.comindico.com.au>...
> OR I should simplify my problem :
> BucketID Time WaterLevel
> 1 9:00 Empty
> 1 10:00 Half
> 1 11:00 Full
> 2 9:20 Empty
> 2 10:00 Full
> The BucketID is primary key.
According to your example data this is not the case. Do you mean BucketID and WaterLevel? In general it is much better to post your ddl and insert statements, instead of ascii tables.This way people can start helping out with your question right away, without having to guess the circumstances.
> 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 10:00
> For the BucketID 2, there is no time recording of 'HALF' full, then it is
> null or blank.
What is wrong with the following?
create table bucket (
bucketid int not null, time time, waterlevel char(5) not null, primary key (bucketid, waterlevel), check (waterlevel in ('Empty', 'Half', 'Full')));
insert into bucket values (1, '9:00' , 'Empty'); insert into bucket values (1, '10:00', 'Half'); insert into bucket values (1, '11:00', 'Full'); insert into bucket values (2, '9:20' , 'Empty'); insert into bucket (bucketid, waterlevel) values (2, 'Half'); insert into bucket values (2, '13:00', 'Full'); insert into bucket values (3, '11:00', 'Empty');insert into bucket values (3, '14:10', 'Half'); insert into bucket values (3, '16:00', 'Full');
- solution by John Gilson in comp.databases, oh and it is better to crosspost
- than to post the same question several times in different forums
BTEmpty.Time AS Empty,
BTHalf.Time AS Half,
BTFull.Time AS "Full"
FROM Bucket AS BTEmpty
Bucket AS BTHalf
ON BTEmpty.BucketID = BTHalf.BucketID AND
BTEmpty.WaterLevel = 'Empty' AND
BTHalf.WaterLevel = 'Half'
Bucket AS BTFull
ON BTFull.BucketID = BTEmpty.BucketID AND
BTFull.WaterLevel = 'Full'
- another solution
select b0.bucketid, min(b1.time), min(b2.time), min(b3.time) from bucket b0, bucket b1, bucket b2, bucket b3 where
b0.bucketid = b1.bucketid and b0.bucketid = b2.bucketid and b0.bucketid = b3.bucketid and b1.waterlevel = 'Empty' and b2.waterlevel = 'Half' and b3.waterlevel = 'Full'
group by b0.bucketid;
John doesnt use an aggregate in his query. Why doesnt he need one? What happens with the other solution if you remove min and group by? What is the difference between the two queries?
What happens if:
delete from bucket where bucketid = 2 and waterlevel = 'Empty'
How can the queries be changed so that they still show info about bucket 2 (Hint : Start with John's query)?
/Lennart Received on Mon Jul 28 2003 - 12:07:08 CEST