From: Robert Klemme (shortcuttergooglemail.com)
Date: Tue Mar 06 2007 - 07:19:12 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

2007/3/6, Beermann, Albert <A.Beermannpiepenbrock.de>:
> Hello Everybody
>
>
>
> Left join problem with maxdb 7.6.00.25 (64 bit on suse linux)
>
>
>
> I have 3 tables stpersonalstamm,lolohnkopf,lolohndaten
>
> The link between stpersonalstamm and lolohnkopf is stpersonalstammid
>
> The link between lolohnkopf and lolohndaten is lolohnkopfid
>
>
>
>
>
> In lolohnkopf there can be 1 to n records for a stpersonalstammid for a
> given lofuerjahr,lofuermonat but don't have to
>
> In lolohndaten there can be 1 to n records for a lolohnkopfid but don't
> have to
>
>
>
> I want to search all records from stpersonalstamm and find out wether
> they have entries in lolohnkopf and in lolohndaten or not
>
>
>
> select * from stpersonalstamm
>
> left join lolohnkopf on stpersonalstamm.stpersonalstammid =
> lolohnkopf.stpersonalstammid
>
> and lolohnkopf.lofuerjahr = ?l_jj and lolohnkopf.lofuermonat = ?l_mm
>
> left join lolohndaten on lolohndaten.lolohnkopfid =
> lolohnkopf.lolohnkopfid
>
> where stpersonalstamm.stnlstammid = ?P_stnlstammid
>
> and stpersonalstamm.staustritt >= ?l_aktdatum
>
> and stpersonalstamm.steintrittnl <= ?l_lastdatum
>
> order by stpersonalstamm.stpersonalstammid "
>
>
>
> I expected a result cursor with all columns from stpersonalstamm ,
>
> all columns from lolohnkopf (filled or null) and all columns from
> lolohndaten (filled or null)
>
>
>
> If there are some records in lohnkopf for a given lofuermonat,lofuerjahr
> the select works as expected.
>
> The lolohnkopf part of the result cursor is filled with correct data or
> null values
>
>
>
> But if there are no records in lolohnkopf for a given
> lofuermonat,lofuerjahr the result cursor is empty ???????????

What do you expect? You use "and lolohnkopf.lofuerjahr = ?l_jj and
lolohnkopf.lofuermonat = ?l_mm" as a filter - if no data matches you
get an empty result. That's the way SQL works.

Regards

robert

--
Have a look: http://www.flickr.com/photos/fussel-foto/

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]