Re: Avoiding Nulls in Returned Records from SQL Select PHP

["Shannon ODonnell" <sodonnell@xxxxxxxxxxxxxxxxxxxxxxxxx>]

Yep.  A lot of joins.    A lot of tables have to be joined together so...


I'm not sure how to convert the LEFT/RIGHT Joins to Inner Joins.

Guess I'll Google that and see what I can figure out.

Thanks!


-----Original Message-----
From: pctech-bounces@xxxxxxxxxxxx [mailto:pctech-bounces@xxxxxxxxxxxx] On
Behalf Of Glenn Hopwood
Sent: Wednesday, July 28, 2010 7:56 AM
To: PC Technical Discussion for iSeries Users
Subject: Re: [PCTECH] Avoiding Nulls in Returned Records from SQL Select PHP

Wow, that's a lot of joins...

I would pursue the following options.
1 - Change the joins to 'inner'. (I believe MySQL defaults to outer if 
you don't specify. Someone will correct me if I'm wrong.)
2 - Use 'coalesce' to replace the NULLs. (Not sure how many fields are 
involved in your select list)
3 - Code your PHP to ignore the nulls.

#3 is where I would start.

Glenn

On 7/27/2010 10:08 PM, Shannon ODonnell 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' " ;