[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL Inner Join conditional value

Posted on 2009-12-16
9
Medium Priority
?
376 Views
Last Modified: 2013-12-12
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

0
Comment
Question by:spferaro
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1000 total points
ID: 26067162
$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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26067171
change:
INNER JOIN cpt ON phy_sec.cpt=cpt.cpt
into:
LEFT JOIN cpt ON phy_sec.cpt=cpt.cpt
0
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 26067174
I think that should be LEFT OUTER JOIN?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26067184
0
 

Author Comment

by:spferaro
ID: 26067248
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
 
LVL 40

Expert Comment

by:Richard Quadling
ID: 26067250
Thanks A3.
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 26067300
please use COALESCE() instead of ISNULL()
0
 

Author Closing Comment

by:spferaro
ID: 31667061
Awesome!!!  Thanks for the help.  COALESCE() iced the cake.  I hope splitting-up the points is OK with you guys.

SPF
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month20 days, 14 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question