Need help with Stored Procedure

Hello Experts,

I have the following Stored Procedure below that I need to retrieve ALL data based on the ghaoc_id value entered even if the value ghaocp_id is NULL from the HealthCourses_CourseSchedules table.

The attached Excel spreadsheet includes all Database schema along with real data that is in my system. You will see that I have NULL values for the ghaocp_id in the HealthCourses_CourseSchedules table. I still need to display ALL to the user even if ghaocp_id has  NULL value.

I'm also attaching my current Stored Procedure that I'm having the trouble with.

Thank in advance!!


@ghaoc_id int


SELECT cs.ghaoc_id, oc.ghaco_name, cs.ghaocp_id, cs.csch_startdate, cs.csch_starttime, cs.csch_endtime, hi.hmi_name, p.ghaocp_name
FROM HealthCourses_CourseSchedules AS cs
INNER JOIN HealthCourses_HighmarkInstructors AS hi
ON cs.hmi_id = hi.hmi_id
INNER JOIN HealthCourses_GeneralHealthAwarenessOneCoursesPrograms AS p
ON cs.ghaocp_id = p.ghaocp_id
INNER JOIN HealthCourses_GeneralHealthAwarenessOneCourses AS oc
ON cs.ghaoc_id = oc.ghaoc_id
WHERE cs.ghaoc_id = @ghaoc_id
ORDER BY ghaocp_name ASC

Open in new window

Who is Participating?
You are using INNER JOIN which means it excludes records where one side is null or where there is no matching record on the joined table (because by default NULL won't match anything, even another NULL). You would want to use LEFT OUTER JOIN to return all records from HealthCourses_CourseSchedules, even where ghaocp_id is NULL or doesn't match the other table. If you want to actually join records based on NULL in the column you should do something with ISNULL.

asp_net2Author Commented:
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.