Hello Michael,

> I have a SQL statement like this:
>
> SELECT
> T1.PLU_NR,
> T2.AFDELING_ID,
> T2.ANTALSTK,
> SUM( T3.ANTAL ),
> SUM( T3.KANTAL )
> FROM T2
> RIGHT OUTER JOIN T1 ON (T2.VAREPLU_ID = T1.PLU_NR)
> LEFT OUTER JOIN T3 ON (T2.VAREPLU_ID = T3.VAREPLU_ID) AND
> (T2.AFDELING_ID = T3.AFDELING_ID)
> WHERE
> (
> (T1.plu_Nr = '000010')
> )
> GROUP BY
> T1.PLU_NR,
> T2.AFDELING_ID,
> T2.ANTALSTK
> ORDER BY
> T1.PLU_NR,
> T2.AFDELING_ID
>
>
> This gives me a result like this:
>
> 000010, 001, 111, <null>, <null>
> 000010, 002, 222, 45, 87
>
> If I then add one more condition like this one:
>
> SELECT
> T1.PLU_NR,
> T2.AFDELING_ID,
> T2.ANTALSTK,
> SUM( T3.ANTAL ),
> SUM( T3.KANTAL )
> FROM T2
> RIGHT OUTER JOIN T1 ON (T2.VAREPLU_ID = T1.PLU_NR)
> LEFT OUTER JOIN T3 ON (T2.VAREPLU_ID = T3.VAREPLU_ID) AND
> (T2.AFDELING_ID = T3.AFDELING_ID)
> WHERE
> (
> (T1.plu_Nr = '000010')
> AND
> (((T3.aar*100)+T3.mdr)>=200401)
> )
> GROUP BY
> T1.PLU_NR,
> T2.AFDELING_ID,
> T2.ANTALSTK
> ORDER BY
> T1.PLU_NR,
> T2.AFDELING_ID
>
>
> Then the first line with the <null> values are left out.
>
> I thought, that when I made an LEFT OUTER JOIN I would get every
> record as in the first eventhough I put on a condition regarding
> table T3.

Not if the resulting T3 values don't match the WHERE clause.

> The result set should have been:
>
> 000010, 001, 111, <null>, <null>
> 000010, 002, 222, 22, 43
>
> But it is:
> 000010, 002, 222, 22, 43
>
>
> I really would like to have to first line present as well !

Well, we don't have the T3.AAR or T3.MDR data, so it's a bit
hard to tell.

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com