Re: Problem with left join select command ??

2007/3/6, Beermann, Albert <[email protected]>:
> 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
To unsubscribe:    http://lists.mysql.com/[email protected]