Re: Left Join Problem -- JAVA400-L

Ron, usually if you only want to get records from table A that exist ontable B then you would not use the left outer join since it will bring allrecords from table A that exist or don't exist in table B. The onlydifferent is that if the table B does not exist then you get NULL values onany fields that you may retrieve from table B.

One way to do this is you check first the value of the fields comming fromtable B and compare with the NULL value and if it is null then assigned yourown value to the field for instance 0 for numeric or blanks to alpha fieldor even "NOT FOUND" or something like that.

On the other hand, you may want to just bring the records from table A thatexist in table B and a separate SQL to retrieve all the records that don'texist in table B. This is good for testing pursposes as of how to know ...


Your SQL will look like this:

select A.CGSTREET#, A.CGSTNAME, B.WCCIVIC#
 from areaTable AS A LEFT JOIN custTable AS B ON
 A.CGSTREET#=B.WCSTREET#
 where NOT exists
 (select * from custTable AS B
     where A.CGSTREET#=B.WCSTREET# )
ORDER BY CGSTREET#, WCCIVIC#

The above SQL statement can be changed then to only deal with the recordthat don' exist by replacing the NOT by a blank


I hope it help...

----- Original Message -----From: <RPower@xxxxxxxxxx>To: "Java Programming on and around the iSeries / AS400"<java400-l@xxxxxxxxxxxx>

Sent: Friday, June 10, 2005 11:42 AM
Subject: Left Join Problem

If I'm doing a left join using JDBC how can I tell if I got only rows from
the left table and none from the right?  I'm getting a null pointer
exception when I have only left table rows?  In the following code,
WCCIVC# is part of the right table, and there are no rows returned for it.
Thanks, code follows:

                       getProps =
                               conn.prepareStatement(
                                       "SELECT A.CGSTREET#, A.CGSTNAME,
B.WCCIVIC# FROM "
                                               + collectionName
                                               +
dmd.getCatalogSeparator()
                                               + areaTable
                                               + " AS A LEFT JOIN "
                                               + collectionName
                                               +
dmd.getCatalogSeparator()
                                               + custTable
                                               + " AS B ON A.CGSTREET# =
B.WCSTREET# ORDER BY CGSTREET#, WCCIVIC#");
                       rs = getProps.executeQuery();
                       Vector vStreet = new Vector();
                       Vector vCivic = new Vector();
                       vStreet.clear();
                       vCivic.clear();
                       int tempStreet = 0;
                       String tempStName = new String();
                       while (rs.next()) {
                               if (tempStreet == 0) {
                                       tempStreet = rs.getInt("CGSTREET#"
);
                                       tempStName = rs.getString(
"CGSTNAME");
                               }
                               if (tempStreet != rs.getInt("CGSTREET#"))
{
                                       vCivic.trimToSize();
                                       StreetInfo st =
                                               new StreetInfo(tempStreet,
tempStName, vCivic);
                                       vStreet.addElement(st);
                                       tempStreet = rs.getInt("CGSTREET#"
);
                                       tempStName = rs.getString(
"CGSTNAME");
                                       vCivic = new Vector();
                                       vCivic.clear();
                               }
                               Integer c =
                                       new Integer(
Integer.parseInt(rs.getString("WCCIVIC#").trim()));
                               if (!vCivic.contains(c)) {
                                       vCivic.addElement(c);
                               }
                       }

Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
Tel: 709-576-8132
Email: rpower@xxxxxxxxxx
Website: http://www.stjohns.ca/
___________________________________________________________________________
Success is going from failure to failure without a loss of enthusiasm. -
Sir Winston Churchill
--

This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)mailing list

To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.