Valid Stored Procedure not returning any results

Question

I have the following Stored Procedure

ALTER PROCEDURE [dbo].[bt_BizForSale_GetByID]
(
@ID int
)
AS
SET NOCOUNT ON

SELECT      dbo.bt_BizForSale.UserID, 
            dbo.bt_BizForSale.VendorType,
            dbo.bt_BizForSale.BusinessName, 
            dbo.bt_BizForSale.isEmailSubscriber,
            dbo.bt_BizForSale.isIntermediarySubscriber, 
            dbo.bt_Regions.Region AS Country, 
            bt_Regions_1.Region AS Province, 
            bt_Regions_2.Region AS City,
            dbo.bt_BizForSale.[AdType]

FROM         dbo.bt_BizForSale INNER JOIN
                      dbo.bt_Regions ON dbo.bt_BizForSale.[61] = dbo.bt_Regions.ID INNER JOIN
                      dbo.bt_Regions AS bt_Regions_1 ON dbo.bt_BizForSale.[62] = bt_Regions_1.ID INNER JOIN
                      dbo.bt_Regions AS bt_Regions_2 ON dbo.bt_BizForSale.[63] = bt_Regions_2.ID

WHERE     (dbo.bt_BizForSale.ID = @ID)

And when I execute it with a VALID ID from the table, it's not returning any results. What could I possibly be missing?

PS: on "most" valid ID's I pass to the stored procedure, I get the results I'm looking for.

Example: 10010 will return results, but 10104 will not. Both are valid records in the database.

Answer 1

Probably a Region ID in the [61], [62], [63] columns which is not in dbo.bt_Regions (or is NULL, which will never satisfy any equality condition)

INNER JOIN requires that the (equality) condition be satisfied - so a row must be found

If any one of your INNER JOINs does not have a satisfied condition, you will get no rows.

You will need to either correct your foreign keys to be valid or change to a LEFT JOIN with appropriate handling of NULLs when the right hand side has no rows satisfying the join criteria