MySQL Inner Join conditional value

Hello Experts!

I'm stumped.  I have created the attached MySQL statement that works perfectly as long as the inner join is successful.  The result is intended to connect a parent and child table on the cpt field.  The majority of the data comes from the parent table and only description of the matching cpt field comes from the child table.  

Here's my problem:  Using my statement as written, if there is not a match, the parent record is not selected.  What I would like to happen is to select the parent record and substitute the description that would have come from the child table with a descriptor like "NO CODE DESCRIPTION AVAILABLE."  Can this be done?  If so, can someone help me with the code to insert that will make the MySql statement do what I need?

Thanks!!!
$sql="SELECT phy_sec.recno,
         phy_sec.inum,
        	phy_sec.prvno,
        	phy_sec.seqno,
        	phy_sec.begsvc,
        	phy_sec.endsvc,
        	phy_sec.cpt,
        	phy_sec.cptmod,
        	cpt.descript,
        	phy_sec.days_units,
        	phy_sec.original,
        	phy_sec.cmpcalc
      FROM phy_sec
      INNER JOIN cpt ON phy_sec.cpt=cpt.cpt
      WHERE phy_sec.inum = '".$inum."' ";

Open in new window

spferaroAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
$sql="SELECT phy_sec.recno,
         phy_sec.inum,
              phy_sec.prvno,
              phy_sec.seqno,
              phy_sec.begsvc,
              phy_sec.endsvc,
              phy_sec.cpt,
              phy_sec.cptmod,
              ISNULL(cpt.descript,'NO CODE DESCRIPTION AVAILABLE' ) as descript,
              phy_sec.days_units,
              phy_sec.original,
              phy_sec.cmpcalc
      FROM phy_sec
      LEFT  JOIN cpt ON phy_sec.cpt=cpt.cpt
      WHERE phy_sec.inum = '".$inum."' ";

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
change:
INNER JOIN cpt ON phy_sec.cpt=cpt.cpt
into:
LEFT JOIN cpt ON phy_sec.cpt=cpt.cpt
0
 
Richard QuadlingSenior Software DeveloperCommented:
I think that should be LEFT OUTER JOIN?
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
spferaroAuthor Commented:
Looks good and makes sense, but here is the error message I am receiving when i try it:

SELECT phy_sec.recno, phy_sec.inum, phy_sec.prvno, phy_sec.seqno, phy_sec.begsvc, phy_sec.endsvc, phy_sec.cpt, phy_sec.cptmod, ISNULL(cpt.descript,'NO CODE DESCRIPTION AVAILABLE') as descript, phy_sec.days_units, phy_sec.original, phy_sec.cmpcalc FROM phy_sec LEFT JOIN cpt ON phy_sec.cpt=cpt.cpt WHERE phy_sec.inum = 'GAL0000005' ORDER BY phy_sec.seqno ASC LIMIT 0, 10
[nativecode=1064 ** You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''NO CODE DESCRIPTION AVAILABLE') as descript, phy_sec.days_units, ' at line 1]

Any thoughts?
0
 
Richard QuadlingSenior Software DeveloperCommented:
Thanks A3.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please use COALESCE() instead of ISNULL()
0
 
spferaroAuthor Commented:
Awesome!!!  Thanks for the help.  COALESCE() iced the cake.  I hope splitting-up the points is OK with you guys.

SPF
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.