CODEWITHSUNDEEP
sqlt-sql
Sperick
Sperick

Reputation: 2761

SQL: Want to alter the conditions on a join depending on values in table

I have a table called Member_Id which has a column in it called Member_ID_Type. The select statement below returns the value of another column, id_value from the same table. The join on the tables in the select statement is on the universal id column. There may be several entries in that table with this same universal id.

I want to adjust the select statement so that it will return the id_values for entries that have member_id_type equal to '7'. However if this is null then I want to return records that have member_id_type equal to '1'

So previously I had a condition on the join (commented out below) but that just returned records that had member_id_type equal to '7' and otherwise returned null.

I think I may have to use a case statement here but I'm not 100% sure how to use it in this scenario

SELECT TOP 1    cm.Contact_Relation_Gid,
                mc.Universal_ID,
                mi.ID_Value,
                cm.First_Name, 
                cm.Last_Name, 
                cm.Middle_Name, 
                cm.Name_Suffix,
                cm.Email_Address,
                cm.Disability_Type_PKID,
                cm.Race_Type_PKID,
                cm.Citizenship_Type_PKID,
                cm.Marital_Status_Type_PKID,
                cm.Actual_SSN,
                cm.Birth_Date, 
                cm.Gender, 
                mc.Person_Code,
                mc.Relationship_Code,
                mc.Member_Coverage_PKID,
                sc.Subscriber_Coverage_PKID,
FROM Contact_Member cm (NOLOCK) 
INNER JOIN Member_Coverage mc (NOLOCK) 
    ON cm.contact_relation_gid = mc.contact_relation_gid 
    AND mc.Record_Status = 'A'
INNER JOIN Subscriber_Coverage sc (NOLOCK) 
    ON mc.Subscriber_Coverage_PKID = sc.Subscriber_Coverage_PKID
    AND mc.Record_Status = 'A'  
LEFT outer JOIN Member_ID mi ON mi.Universal_ID = cm.Contact_Gid
    --AND  mi.Member_ID_Type_PKID='7'
WHERE cm.Contact_Relation_Gid = @Contact_Relation_Gid
AND cm.Record_Status = 'A'

Upvotes: 1

Views: 50

Answers (1)

Andomar
Andomar

Reputation: 238276

Join them both, and use one if the other is not present:

select  bt.name
,       coalesce(eav1.value, eav2.value) as Value1OrValue2
from    BaseTable bt
left join EavTable eav1
on      eav1.id = bt.id
        and eav1.type = 1
left join EavTable eav2
on      eav2.id = bt.id
        and eav2.type = 2

This query assumes that there is never more than one record with the same ID and Type.

Upvotes: 1

Related Questions