Help with a query

[LesStockton]

I've got the following query, which works fine, except in the case where one lookup field might be null. Null is allowed.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





ALTER PROCEDURE [dbo].[udFollowupByContactID]
@ContactID int
AS
SELECT
FollowupID AS "FollowupID",
T_Followup.ContactID AS "ContactID",
T_Followup.FollowupReasonTypeID AS "FollowupReasonTypeID",
T_FollowupReasonType.ReasonDescription as "FollowupReasonDescription",
FollowupRepID AS "FollowupRepID",
(T_Contact.Lastname + ', ' + T_Contact.Firstname) As "AssignedTo",
DueDate AS "Due",
CompleteDate AS "CompleteDate",
RequestText AS "RequestText",
DirectContactText AS "DirectContactText",
T_Followup.CancelledInd AS "CancelledInd",
CanceledDate AS "CanceledDate",
T_Followup.OwnerCompany AS "FollowupOwnerCompany",
T_Followup.CreateUser AS "FollowupCreateUser",
T_Followup.UpdateUser AS "FollowupUpdateUser",
T_Followup.CreateDate AS "FollowupCreateDate",
T_Followup.UpdateDate AS "FollowupUpdateDate"
FROM [T_Followup], [T_FollowupReasonType], [T_Contact]
WHERE T_Followup.ContactID = @ContactID
AND T_FollowupReasonType.FollowupReasonTypeID = T_Followup.FollowupReasonTypeID
AND T_Contact.ContactID = T_Followup.FollowupRepID

I'm populating AssignedTo with results from the T_Contact table, but in the event that no FollowupRepID exists, I still want that record, because ContactID matches.

 

[SQLBill]

You probably should be using JOINS.

FROM [T_Followup]
 LEFT OUTER JOIN [T_FollowupReasonType]
   ON T_Followup.FollowupReasonTypeID = T_FollowupReasonType.FollowupReasonTypeID 
 RIGHT OUTER JOIN [T_Contact]
   ON T_Followup.FollowupRepID = T_Contact.ContactID 
WHERE T_Followup.ContactID = @ContactID

I think that might work.
-SQLBill

Posting advice: FAQ481-4875

 

[LesStockton]

same result.
I still don't get a record if the FollowupRepID is null.
I should get those. It's just that if it's not null, I want to get the name from the T_Contact table.