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]
