Mailing List
Home
Forum Home
MySQL General - General MySQL discussion
MySQL++ - Programming with the C++ API to MySQL
MaxDB - Everything about MaxDB, formerly known as SAP DB
ODBC - ODBC with the MySQL Connector/ODBC driver
MySQL on Win32 - Runing MySQL on Windows 9x/Me/NT/2000/XP
Java Help - Mostly related to the MySQL Connector/J driver
Perl - Perl support for MySQL with DBI and DBD::mysql
GUI - MySQL GUI Tools
Announcement
Subjects
• mysql openssl Question
• mysql:it 's a db not a dbms how it 's possible?!
• Does the binary log enabling affect the MySQL performances?
• Strange behavior, Table Level Permission
• FULLTEXT query format question
• Preventing Duplicate Entries
• Comparing and writing out BLOBS
• Executing MySQL Commands From Within C Program
• Can 't access mysql after kernel upgrade
• Mysql 4 0 Oracle Stored Procedure Trigger Conversion
• Downgrade Mysql from 4 to 3 23
• MySQL Cluster Software
• mysql test 4 1 fails with the gis test
• ERROR 2002: Can 't connect to local MySQL server through socket
• Getting Identity after INSERT
• Update one field with more fields from another table
• ERROR 1045: Access denied for user: 'root@localhost ' (Using
password: NO)
• mysql have same function mthod as Oracle decode()
 
Problem with left join select command ??

Problem with left join select command ??

2007-03-06       - By Beermann, Albert

 Back
Reply:     1     2     3  

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 ???????????




Any help welcomed



Best regards

Albert