Decision Statement

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.
Joshitry this...
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&&&-- Filter for
Relationship =2 or MaritalStatusID = 1--
or fr.RelationshipID = 2 or maritalstatusid = 1
"J. Joshi" wrote:

> 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
>|||On Tue, 8 Feb 2005 10:41:46 -0800, J. Joshi wrote:

>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?
Hi Joshi,
You can do the selection in the WHERE clause of the query:
WHERE (other requirements)
AND ( RelationshipID = 2
OR (RelationshipID IS NULL AND ISNULL(MaritalStatusID, 1) = 1))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)