Including IF in a where clause

Question

I need to include an IF or a CASE statement in a where clause that will give me a record dependant on the data in the db.

what I want to do is select a certain contact that has a certain code in relationship_code_id to indicate they have been assigned POA (code is 45). If the contact has not got POA, then display the main contact (main_contact = 1). However, any contact that has POA takes priority over a main contact. So if Joe Bloggs was assigned the main contact, but Anne Nickols was assigned POA, the show Anne not Joe.

(This information is included in the TNTGROUP table)

Is there anyway I can do this?

Apologies if this does not make sense.

This is my code below:

SELECT 

     TNTTITLE.Description_VC AS TITLE
    ,TNTCONTACT.Forename_VC AS FORENAME
    ,TNTCONTACT.Surname_VC AS SURNAME
    ,TNTCONTACTEMAIL.Reference_VC AS EMAIL  
    ,TNTHOMENUMBER_GRP.Reference_VC AS GROUP_HOME_NUMBER
   ,RENTACC.ID AS RENT_ACCOUNT_ID
   ,CONVERT(DATE,RENTACC.AccountStartDate) AS RENT_ACCOUNT_START_DT
   ,CASE
        WHEN LETOFFERS.Current_Status_ID = 42 THEN 'Complete'
    ELSE 'OTHER'
    END 
        AS STATUS
    ,ASSETTYPES.Description_VC AS ASSET_NAME
    ,CASE WHEN (SELECT MainAsset FROM RENTACC HAVING COUNT(*) > 1) THEN 1 ELSE 0 END


   FROM RENT_ACCOUNTS AS RENTACC

    INNER JOIN LETTING_OFFERS_T AS LETOFFERS
        ON RENTACC.ID = LETOFFERS.Rent_Account_Reference_VC
            AND RENTACC.AccountEndDate IS NULL
            AND RENTACC.AccountType = 'T'

    LEFT JOIN Letting_SYS_Master_T AS LETCODES
        ON LETOFFERS.Current_Status_ID = LETCODES.CODE_ID
            AND LETCODES.Type_ID = '14'

    LEFT JOIN Asset_Assets_T AS ASSETS
        ON RENTACC.MainAsset = ASSETS.Asset_ID

    LEFT JOIN Asset_Types_T AS ASSETTYPES
        ON ASSETS.Asset_TYPE_ID = ASSETTYPES.Asset_Type_ID


    /**** MAIN CONTACT INFORMATION ****/

    LEFT JOIN Shared_Addresses_T AS TNTADDRESS
        ON ASSETS.Address_ID = TNTADDRESS.Address_ID

    LEFT JOIN Contact_Group_Contacts_T AS TNTGROUP
        ON RENTACC.ContactDatabaseReference = TNTGROUP.GROUP_ID
            AND TNTGROUP.Main_Group_Contact_BT = 1
            AND TNTGROUP.Removed_BT = 0

    LEFT JOIN CONTACT_CONTACTS_T AS TNTCONTACT
        ON TNTGROUP.Contact_ID = TNTCONTACT.Contact_ID
            AND TNTCONTACT.Current_Status_ID = 65
            AND TNTCONTACT.Deceased_Date_DT IS NULL 

    LEFT JOIN Shared_Codes_T AS TNTTITLE
        ON TNTCONTACT.Title_Code_ID = TNTTITLE.Code_ID
            AND TNTTITLE.Code_ID IN (103,104,105,106)

    /**** CONTACT EMAIL INFORMATION ****/

    LEFT JOIN Contact_Contact_Details_T AS TNTCONTACTEMAIL
        ON TNTCONTACT.Contact_ID = TNTCONTACTEMAIL.Contact_ID
            AND TNTCONTACTEMAIL.SYS_Number_Code_ID = 75
            AND TNTCONTACTEMAIL.Expiry_Date_DT IS NULL


    /**** GROUP HOME NUMBER INFORMATION  ****/

    LEFT JOIN Contact_Group_Details_T AS TNTHOMENUMBER_GRP
        ON RENTACC.ContactDatabaseReference = TNTHOMENUMBER_GRP.Group_ID
            AND TNTHOMENUMBER_GRP.Expiry_Date_DT IS NULL
            AND TNTHOMENUMBER_GRP.SYS_Number_Code_ID = 71

It's generally better to use AND/OR instead of case/if in WHERE and ON clauses. — jarlh, Mar 13 '17 at 14:57
May you please minimize your code to only show us what you need help with. really difficult to read and understand 101 joins — Stephen, Mar 13 '17 at 15:13
@jarlh and even better not to use any of them as my answer :D — Hogan, Mar 13 '17 at 15:13
@Hogan, don't try to fool me, I see an `AND` in your answer's `ON` clause. ;) — jarlh, Mar 13 '17 at 15:16

score 0 · Answer 1 · answered Mar 13 '17 at 15:10

I would do it like this -- I would left join to the POA contact information with part of the join checking the code id for 45 -- thus it will be null if the code id is not 45. Then I just use COALESCE to pull the value needed. You don't give a lot of information about your data model but it should look like this:

No IF or CASE needed -- it is all built into the filter on the join which all SQL platform are optimized for evaluating.

SELECT 
  -- blah blah blah.. current select
  -- TNTGROUP won't be null if relationship code is 45
  COALESCE(TNTGROUP.CONTACT_INFO,a_table.main_contact) as Contact
FROM a_table
LEFT JOIN TNTGROUP ON 
   -- How TNTGROUP joins to a_table (which you don't say)
   a_table.id = TNTGROUP.id
   -- Only if valid
   AND TNTGROUP.relationship_code_id = 45

Including IF in a where clause

1 Answers1