Fwd: Avoiding Nulls in Returned Records from SQL Select PHP

[Tom Jedrzejewicz <tomjedrz@xxxxxxxxxxxxxx>]

I couldn't help but think of the Beach Boys ... "she's giving me
excitations ..."

----
Tom Jedrzejewicz

On Jul 27, 2010, at 7:08 PM, "Shannon ODonnell"
<sodonnell@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

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