CODEWITHSUNDEEP
sqlsql-servert-sqlselect
Chuck Brown
Chuck Brown

Reputation: 13

Left outer join multiple selects

I have the following sql statement, that does exactly what it should:

select    C.Company_RecID, C.Contact_RecID, C.First_Name, C.Last_Name,
          C.Title, C.Inactive_Flag, e.Description
FROM            dbo.Contact AS C 
LEFT OUTER JOIN dbo.Contact_Communication AS e ON C.Contact_RecID = e.Contact_RecID 
                                AND e.Communication_Type_RecID = 1 AND e.Default_Flag = 1

However, in dbo.contact_communication, the description field means different things depending upon the communication_type_recID. If it's 1, description is an email address. If it's 4, it's a cell phone number, if it's 2, it's a direct number, and if it's 14, it's a personal cell phone number.

I'd like to change the query to return three additional columns. Column 8 would be the value of description if there is a record for this contact with a recid of 4, column 9 the value of description if there is a record for this contact with a recid of 2, and column 10 if there is a value of 14.

Upvotes: 0

Views: 220

Answers (3)

paparazzo
paparazzo

Reputation: 45106

Without aggregation 

select C.Company_RecID
     , C.Contact_RecID
     , C.First_Name
     , C.Last_Name
     , C.Title
     , C.Inactive_Flag
     , e.Description
     , e1.Description  as EmailAddress
     , e2.Description  as CellPhone
     , e4.Description  as DirectNumber
     , e14.Description as PersonalCellPhone 
  FROM dbo.Contact AS C 
  LEFT OUTER JOIN dbo.Contact_Communication AS e1 
    ON e1.Contact_RecID = C.Contact_RecID   
   AND e1.Communication_Type_RecID = 1 
   AND e1.Default_Flag = 1
 LEFT OUTER JOIN dbo.Contact_Communication AS e2 
    ON e2.Contact_RecID = C.Contact_RecID   
   AND e2.Communication_Type_RecID = 2
   AND e2.Default_Flag = 1
  LEFT OUTER JOIN dbo.Contact_Communication AS e4 
    ON e4.Contact_RecID = C.Contact_RecID   
   AND e4.Communication_Type_RecID = 4 
   AND e4.Default_Flag = 1
  LEFT OUTER JOIN dbo.Contact_Communication AS e14 
    ON e14.Contact_RecID = C.Contact_RecID   
   AND e14.Communication_Type_RecID = 14  
   AND e14.Default_Flag = 1

Upvotes: 0

Sean Lange
Sean Lange

Reputation: 33581

Something like this should be pretty close as I understand what you are looking for.

select C.Company_RecID
    , C.Contact_RecID
    , C.First_Name
    , C.Last_Name
    , C.Title
    , C.Inactive_Flag
    , e.Description
    , EmailAddress = MAX(case when communication_type_recID = 1 then e.Description end)
    , CellPhone = MAX(case when communication_type_recID = 4 then e.Description end)
    , DirectNumber = MAX(case when communication_type_recID = 2 then e.Description end)
    , PersonalCellPhone = MAX(case when communication_type_recID = 14 then e.Description end)
FROM dbo.Contact AS C 
LEFT OUTER JOIN dbo.Contact_Communication AS e ON C.Contact_RecID = e.Contact_RecID 
                                        AND e.Communication_Type_RecID IN (1,2,4,14) 
                                        AND e.Default_Flag = 1
group by C.Company_RecID
    , C.Contact_RecID
    , C.First_Name
    , C.Last_Name
    , C.Title
    , C.Inactive_Flag
    , e.Description

Upvotes: 1

Roger D
Roger D

Reputation: 46

I think UNION might help you. (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-2017)

You can try something like this:

select col_1, 'something' as A, '' as B from TableA
union
select col_2, '' as A, 'something' as B from TableA

col_1 and col_2 are actual columns in the table, while A and B are aliases for this extra information.

Upvotes: 0

Related Questions