Re: Missing numbers

On Wed, Jun 01, 2005 at 01:21:28AM +0100, Simon Riggs wrote:
> On Tue, 2005-05-31 at 18:28 +0200, Harald Fuchs wrote:

> > SELECT g.num
> > FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
> >                       (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> > LEFT JOIN bdocs ON bdocs.doc_numero = g.num
> > WHERE bdocs.doc_numero IS NULL
>
> SELECT g.num
> FROM generate_series ((SELECT min(doc_numero) FROM bdocs),
>                       (SELECT max(doc_numero) FROM bdocs)) AS g(num)
> WHERE g.num NOT IN (select doc_numero
>            from bdocs
>            where doc_numero is not null)
>
> is more likely to return a correct answer, since
> bdocs.doc_numero will never equal g,num when it is also NULL

Oh, but it is an outer join, so it should generate the NULLs, yes?

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Vivir y dejar de vivir son soluciones imaginarias.
La existencia está en otra parte" (Andre Breton)