Decision Statement

From: J. Joshi (jjoshi_at_uhs.harvard.edu)
Date: 02/08/05 

Date: Tue, 8 Feb 2005 10:41:46 -0800

Hello all,

I need to generate a result based on 2 fields:

1. RelationshipID &
2. MaritalStatusID

The above fields are not embedded in the application as a
mandatory check box so some of the values maybe empty. I
need to get a list of all the members who have a
RelationshipID = 2 and if it is NULL look in
MaritalStatusID = 1 and if that is NULL, display the
result anyways. I do not need to show the values from
these fields as they are a part of my parameters (filters)
in the query. I am not a programmer and wanted to know how
would I write this in a IF THEN statement in SQL?

select Distinct pip.MemberID

from tblMemberInsurancePlan pip
Inner Join tblMember m on m.MemberID = pip.MemberID
Left Join ctblInsurancePlan ip on ip.InsurancePlanID =
pip.InsurancePlanID
Left Join ctblFamilyRelationship fr on fr.RelationshipID =
pip.RelationToSubscriber
Left Join ctblMaritalStatus ms on ms.MaritalStatusID =
m.MaritalStatusID
 
-- Filtering for Current insurance only
where (pip.InsurancePlanState in ('C') and
pip.InsurancePlanOrdinal = 1)
-- Filtering for Dependents only
and pip.MemberID <> pip.InsurancePlanSubscriberID
-- Filtering for 'CHILD' relationship to the subscriber
(and fr.RelationshipID = 2 or
-- Filter for Relationship = 2 or MaritalStatusID = 1
and ????????????????????

I greatly appreciate any help in architecting the code in
this regard.

Thansk again.

Joshi