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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

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!!
STORED PROCEDURE:

ALTER PROCEDURE [dbo].[TEST2]

(
@ghaoc_id int
)

AS

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

Book1.xls
0
asp_net2
Asked:
asp_net2
1 Solution
 
stpriorCommented:
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.

0
 
asp_net2Author Commented:
Thanks
0

Featured Post

Industry Leaders: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now