× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. PCTECH
  3. July 2010

Re: Avoiding Nulls in Returned Records from SQL Select PHP

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' " ;



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.