Fwd: Avoiding Nulls in Returned Records from SQL Select PHP

Hi,



So.I have what is, for me anyway, a fairly complex SQL select statement over
a set of MySQL tables which uses RIGHT and LEFT JOINS to search about 18
tables.



The Select works in general.except for one thing. ÂI'm getting NULL records
and I don't want them. J



In the MySQL tables, I have created a common key that exists in all of the
tables named ÂCaseID.



I am using a RIGHT and LEFT JOIN to get the data from these files.



Some of the tables in my JOIN(s) do not have records in them that match the
selection criteria while others do have matching data.



If I run my SQL statement outside of PHP, say within the MySQL Admin page
(if you're using WAMP for example).the records are returned, but I see the
null records from the tables that do not have any records in them that match
the selection criteria.



When I run this same SELECT in PHP, Âand I try to access the value of the
common field CaseID, ÂI'm getting a null in that field value because I'm
getting the last record the SELECT returned, which is a NULL record because
there wasn't any matching record in that particular table so that common
field name is blank/null.



What I really want to do is to be able to run my SQL statement and NOT
return NULL (empty) records.



That is.if I have 3 tables, and the first table and the third table each
have a record that matches my selection criteria, but the second table does
not, ÂI only want to return the data from the first and the last table.



I know I could give unique names to the field CaseID (and any other fields
that are the same in multiple tables) but I'm hoping to not have to do that
since I'd have to make changes in a lot of places to implement that
solution. ÂI'd rather just avoid returning NULL records if I can.



I have included the actual SQL statement here, in its entirety, but probably
if you know how to do what I'm looking to do, you don't need to see my SQL
anyway. ÂHowever, having it here will help someone else later on when they
search the archives for a similar solution.



Thanks in advance for all suggestions!



Shannon O'Donnell





       Â$query = Â"SELECT * ÂFROM xcitations

                  ÂLEFT JOIN xcitationbonds ON


xcitations.XmittalBatchnum=xcitationbonds.XmittalBatchNum

                                 AND
xcitations.CaseID = xcitationbonds.CaseID

                                  AND
xcitations.CitationID = Âxcitationbonds.CitationID

                        ÂLEFT JOIN
xcitationaccidenttypes ON


xcitations.XmittalBatchnum=xcitationaccidenttypes.XmittalBatchNum

                                 AND
xcitations.CaseID = xcitationaccidenttypes.CaseID

                                  AND
xcitations.CitationID = Âxcitationaccidenttypes.CitationID

                        ÂLEFT JOIN
xcitationinvolveds ON


xcitations.XmittalBatchnum=xcitationinvolveds.XmittalBatchNum

                                 AND
xcitations.CaseID = xcitationinvolveds.CaseID

                                  AND
xcitations.CitationID = Âxcitationinvolveds.CitationID

                        ÂLEFT JOIN xcitationmethods
ON


xcitations.XmittalBatchnum=xcitationmethods.XmittalBatchNum

                                 AND
xcitations.CaseID = xcitationmethods.CaseID

                                  AND
xcitations.CitationID = xcitationmethods.CitationID


                        ÂLEFT JOIN
xcitationroadconditions ON


xcitations.XmittalBatchnum=xcitationroadconditions.XmittalBatchNum

                                AND
xcitations.CaseID = xcitationroadconditions.CaseID

                                 AND
xcitations.CitationID = Âxcitationroadconditions.CitationID

                        ÂLEFT JOIN
xcitationvehicles ON


xcitations.XmittalBatchnum=xcitationvehicles.XmittalBatchNum

                                AND
xcitations.CaseID = xcitationvehicles.CaseID

                                 AND
xcitations.CitationID = xcitationvehicles.CitationID

                        ÂLEFT JOIN
xcitationviolations ON


xcitations.XmittalBatchnum=xcitationviolations.XmittalBatchNum

                                AND
xcitations.CaseID = xcitationviolations.CaseID

                                 AND
xcitations.CitationID = Âxcitationviolations.CitationID

                        ÂLEFT JOIN
xcitationvisibility ON


xcitations.XmittalBatchnum=xcitationvisibility.XmittalBatchNum

                                AND
xcitations.CaseID = xcitationvisibility.CaseID

                                ÂAND
xcitations.CitationID = xcitationvisibility.CitationID

                ÂLEFT JOIN xcitationwitnesses ON


xcitations.XmittalBatchnum=xcitationwitnesses.XmittalBatchNum

                                ÂAND
xcitations.CaseID = xcitationwitnesses.CaseID

                                 ÂAND
xcitations.CitationID = xcitationwitnesses.CitationID

                        ÂLEFT JOIN xoverweight ON


xcitations.XmittalBatchnum=xoverweight.XmittalBatchNum

                                ÂAND
xcitations.CaseID = xoverweight.CaseID

                                 AND
xcitations.CitationID = xoverweight.CitationID

                         LEFT JOIN
xoverweightaxles ON


xcitations.XmittalBatchnum=xoverweightaxles.XmittalBatchNum


AND xcitations.CaseID = xoverweightaxles.CaseID


AND xcitations.CitationID = xoverweightaxles.XOverWeightAxlesAxleID

                        ÂLEFT JOIN
xoverweightcommchks ON


xcitations.XmittalBatchnum=xoverweightcommchks.XmittalBatchNum


AND xcitations.CaseID = xoverweightcommchks.CaseID


AND xcitations.CitationID = xoverweightcommchks.XOverweightCommChkCommChkID

                        ÂLEFT JOIN
xoverweightcreditcards ON


xcitations.XmittalBatchnum=xoverweightcreditcards.XmittalBatchNum


AND xcitations.CaseID = xoverweightcreditcards.CaseID


AND xcitations.CitationID =
xoverweightcreditcards.XOverweightCreditcardsCreditCardID

                        ÂLEFT JOIN
xoverweightinvolveds ON


xcitations.XmittalBatchnum=xoverweightinvolveds.XmittalBatchNum


AND xcitations.CaseID = xoverweightinvolveds.CaseID


AND xcitations.CitationID =
xoverweightinvolveds.XOverweightInvolvedsInvolvedID


                        ÂLEFT JOIN
xoverweightscales ON


xcitations.XmittalBatchnum=xoverweightscales.XmittalBatchNum


AND xcitations.CaseID = xoverweightscales.CaseID


AND xcitations.CitationID = xoverweightscales.XOverweightScalesScaleID

                        ÂLEFT JOIN
xoverweightviolations ON


xcitations.XmittalBatchnum=xoverweightviolations.XmittalBatchNum


AND xcitations.CaseID = xoverweightviolations.CaseID


AND xcitations.CitationID =
xoverweightviolations.XOverweightViolationsViolationID



                WHERE xcitations.XmittalBatchNum
='".$sBatchNumber."' AND xcitations.XProcessed != 'U' Â UNION



                SELECT * FROM xcitations

                         RIGHT JOIN xcitationbonds
ON


xcitations.XmittalBatchnum=xcitationbonds.XmittalBatchNum

                                 AND
xcitations.CaseID = xcitationbonds.CaseID

                                  AND
xcitations.CitationID = Âxcitationbonds.CitationID

                        ÂRIGHT JOIN
xcitationaccidenttypes ON


xcitations.XmittalBatchnum=xcitationaccidenttypes.XmittalBatchNum

                                 AND
xcitations.CaseID = xcitationaccidenttypes.CaseID

                                  AND
xcitations.CitationID = Âxcitationaccidenttypes.CitationID

                        ÂRIGHT JOIN
xcitationinvolveds ON


xcitations.XmittalBatchnum=xcitationinvolveds.XmittalBatchNum

                                 AND
xcitations.CaseID = xcitationinvolveds.CaseID

                                  AND
xcitations.CitationID = Âxcitationinvolveds.CitationID

                        RIGHT JOIN xcitationmethods
ON


xcitations.XmittalBatchnum=xcitationmethods.XmittalBatchNum

                                 AND
xcitations.CaseID = xcitationmethods.CaseID

                                  AND
xcitations.CitationID = xcitationmethods.CitationID


                        RIGHT JOIN
xcitationroadconditions ON


xcitations.XmittalBatchnum=xcitationroadconditions.XmittalBatchNum

                                AND
xcitations.CaseID = xcitationroadconditions.CaseID

                                 AND
xcitations.CitationID = Âxcitationroadconditions.CitationID

                       ÂRIGHT JOIN xcitationvehicles
ON


xcitations.XmittalBatchnum=xcitationvehicles.XmittalBatchNum

                                AND
xcitations.CaseID = xcitationvehicles.CaseID

                                 AND
xcitations.CitationID = xcitationvehicles.CitationID

                       ÂRIGHT JOIN
xcitationviolations ON


xcitations.XmittalBatchnum=xcitationviolations.XmittalBatchNum

                                AND
xcitations.CaseID = xcitationviolations.CaseID

                                 AND
xcitations.CitationID = Âxcitationviolations.CitationID

                       ÂRIGHT JOIN
xcitationvisibility ON


xcitations.XmittalBatchnum=xcitationvisibility.XmittalBatchNum

                                AND
xcitations.CaseID = xcitationvisibility.CaseID

                                ÂAND
xcitations.CitationID = xcitationvisibility.CitationID

               ÂRIGHT JOIN xcitationwitnesses ON


xcitations.XmittalBatchnum=xcitationwitnesses.XmittalBatchNum

                                ÂAND
xcitations.CaseID = xcitationwitnesses.CaseID

                                 ÂAND
xcitations.CitationID = xcitationwitnesses.CitationID

                       ÂRIGHT JOIN xoverweightaxles
ON


xcitations.XmittalBatchnum=xoverweightaxles.XmittalBatchNum


AND xcitations.CaseID = xoverweightaxles.CaseID


AND xcitations.CitationID = xoverweightaxles.XOverWeightAxlesAxleID

                        RIGHT JOIN
xoverweightcommchks ON


xcitations.XmittalBatchnum=xoverweightcommchks.XmittalBatchNum


AND xcitations.CaseID = xoverweightcommchks.CaseID


AND xcitations.CitationID = xoverweightcommchks.XOverweightCommChkCommChkID

                        RIGHT JOIN
xoverweightcreditcards ON


xcitations.XmittalBatchnum=xoverweightcreditcards.XmittalBatchNum


AND xcitations.CaseID = xoverweightcreditcards.CaseID


AND xcitations.CitationID =
xoverweightcreditcards.XOverweightCreditcardsCreditCardID

                        ÂRIGHT JOIN
xoverweightinvolveds ON


xcitations.XmittalBatchnum=xoverweightinvolveds.XmittalBatchNum


AND xcitations.CaseID = xoverweightinvolveds.CaseID


AND xcitations.CitationID =
xoverweightinvolveds.XOverweightInvolvedsInvolvedID


                        ÂRIGHT JOIN
xoverweightscales ON


xcitations.XmittalBatchnum=xoverweightscales.XmittalBatchNum


AND xcitations.CaseID = xoverweightscales.CaseID


AND xcitations.CitationID = xoverweightscales.XOverweightScalesScaleID

                        ÂRIGHT JOIN
xoverweightviolations ON


xcitations.XmittalBatchnum=xoverweightviolations.XmittalBatchNum


AND xcitations.CaseID = xoverweightviolations.CaseID


AND xcitations.CitationID =
xoverweightviolations.XOverweightViolationsViolationID

      WHERE xcitations.XmittalBatchNum ='".$sBatchNumber."' AND
xcitations.XProcessed != 'U' " ;

--
This is the PC Technical Discussion for iSeries Users (PcTech) mailing list
To post a message email: PcTech@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/pctech
or email: PcTech-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/pctech.