Re: difficult query

On Sat, 4 May 2002 19:34:03 +0200 (CEST)
Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz> wrote:

> > Basically, no. (You might be able to do it with the help
> > of a user-defined function though).

I also think so.


> yesterday when I faced this problem (which can be solved perfectly with
> the outer join) I wasn't able to remember the solution... If there is
> any...  Just out of curiosity...
I wouldn't think that that can be always solved. Whether it can be or notdepends on the elements of  "day" column.  But
untilits aggregation will lack two successive numbers or more, the following query is useful,I guess. 
 


CREATE VIEW v_table1 AS  SELECT tt.day , COUNT(tt.data) AS data    FROM (SELECT t.day, t.data FROM table1 AS t
UNIONALL          SELECT 1, NULL -- indispensable if there's no row of day=1.         )AS tt   GROUP BY tt.day
 
; 
SELECT t0.day, t0.data      FROM v_table1 AS t0
UNION
SELECT t1.day - 1 , 0     FROM v_table1 AS t1, v_table1 AS t2    WHERE t1.day > t2.day    GROUP BY t1.day   HAVING
t1.day> MAX(t2.day) + 1
 
;


In addition, until its aggregation will lack THREE ones or more, ...

SELECT t0.day, t0.data      FROM v_table1 AS t0
UNION
SELECT t1.day - 1, 0      FROM  ...
UNION
SELECT t3.day - 2, 0      FROM  ...



Regards,
Masaru Sugawara