Hello

when I use this:

Select zahl,count(zahl) AS Anzahl,
      max(runde)AS Letzte_Runde,max(runde)
      -(select max(runde) from ZAHLEN) AS Differenz
      FROM total
      group by zahl

then I get

1  3   ...........
2  2   ...........
4  2   ...........        <==== 3 is missing, that is correct
5  2   ...........
.....

when I use this:

Select zahl,count(zahl) AS Anzahl,
      max(runde)AS Letzte_Runde,max(runde)
      -(select max(runde) from ZAHLEN) AS Differenz
      FROM zahl
      left join total                        <======
      on zahl.zahl = total.zahl
      group by zahl

then I get this:

1  3 .........
2  2 .........
3  1 .........          <===== that is not correct!
4  2 .........
5  2 .........
.......

anything in the second SQL ist wrong, but what?

Please help
Dieter

Dieter,

you have different tables in the FROM clause (total and zahl)...depending on the data in
that tables, the results of each query could be different.
Sure you don't want the 2nd query to be "FROM total LEFT JOIN zahl" ?

Heiko

Heiko,

no, the table zahl is a table from 1............
I cannot change the left join

Dieter







Heiko Knuettel <heiko@knuettel.com> wrote:

Dieter,

you have different tables in the FROM clause (total and zahl)...depending on the data in
that tables, the results of each query could be different.
Sure you don't want the 2nd query to be "FROM total LEFT JOIN zahl" ?

Heiko

Diete

>
> Select zahl,count(zahl) AS Anzahl,
>        max(runde)AS Letzte_Runde,max(runde)
>        -(select max(runde) from ZAHLEN) AS Differenz
>        FROM total
>        group by zahl

> 2  2   ...........
> 4  2   ...........        <==== 3 is missing, that is correct
> 5  2   ...........
> ....
>
> when I use this:
>
> Select zahl,count(zahl) AS Anzahl,
>        max(runde)AS Letzte_Runde,max(runde)
>        -(select max(runde) from ZAHLEN) AS Differenz
>        FROM zahl
>        left join total                        <======
>        on zahl.zahl = total.zahl
>        group by zahl
>
> then I get this:
>
> 1  3 .........
> 2  2 .........
> 3  1 .........          <===== that is not correct!
> 4  2 .........
> 5  2 .........

You should be getting

3  Null

Do youi have a small set of tables which shows this?  I tried it here and I
get a null if there are no records in the "total" table.

John

John,

sure, where should I upload the tables?

Dieter





"John Hay" <j.haywithoutdot@crbsolutionsremoveallthis.co.uk> wrote:

Diete

>
> Select zahl,count(zahl) AS Anzahl,
>        max(runde)AS Letzte_Runde,max(runde)
>        -(select max(runde) from ZAHLEN) AS Differenz
>        FROM total
>        group by zahl

> 2  2   ...........
> 4  2   ...........        <==== 3 is missing, that is correct
> 5  2   ...........
> ....
>
> when I use this:
>
> Select zahl,count(zahl) AS Anzahl,
>        max(runde)AS Letzte_Runde,max(runde)
>        -(select max(runde) from ZAHLEN) AS Differenz
>        FROM zahl
>        left join total                        <======
>        on zahl.zahl = total.zahl
>        group by zahl
>
> then I get this:
>
> 1  3 .........
> 2  2 .........
> 3  1 .........          <===== that is not correct!
> 4  2 .........
> 5  2 .........

You should be getting

3  Null

Do youi have a small set of tables which shows this?  I tried it here and I
get a null if there are no records in the "total" table.

John

Dieter,

<< sure, where should I upload the tables? >>

If they can be put in a public place, then the Binaries newsgroup here will
be fine.  I'd like to take a look at this also.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

sorry I tried it to send two times, but I cant.


Dieter






"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Dieter,

<< sure, where should I upload the tables? >>

If they can be put in a public place, then the Binaries newsgroup here will
be fine.  I'd like to take a look at this also.

--
Tim Young
Elevate Software
www.elevatesoft.com

Dieter,

<< sorry I tried it to send two times, but I cant. >>

Did you try to email it to me, or did you try to post in the Binaries
newsgroup ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

I sent it to the binaries. At this time I tried it once again, but I can't.

I send it now to <timyoung@elevatesoft.com>.

TIA
Dieter







"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote:

Dieter,

<< sorry I tried it to send two times, but I cant. >>

Did you try to email it to me, or did you try to post in the Binaries
newsgroup ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Dieter,

<< I sent it to the binaries. At this time I tried it once again, but I
can't.

I send it now to <timyoung@elevatesoft.com>. >>

Hmm, using your data and 2.00, I get the correct results also - a NULL for
the third row/second column value.

--
Tim Young
Elevate Software
www.elevatesoft.com