Re: Urgent help

From: Lennart Jonsson <lennart_at_kommunicera.umea.se>
Date: 28 Jul 2003 03:07:08 -0700
Message-ID: <6dae7e65.0307280207.62e12f8a_at_posting.google.com>


"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

SELECT BTEmpty.BucketID,

               BTEmpty.Time AS Empty,
               BTHalf.Time AS Half,
               BTFull.Time AS "Full"
FROM Bucket AS BTEmpty
            INNER JOIN
            Bucket AS BTHalf
            ON BTEmpty.BucketID = BTHalf.BucketID AND
                   BTEmpty.WaterLevel = 'Empty' AND
                   BTHalf.WaterLevel = 'Half'
            INNER JOIN
            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

Original text of this message