sql query get multiple values from same column for one row

Question

I'm getting multiple rows per staff if they have multiple contact values, but I just want one row per staff listing their Last Name, First Name, Division Name, Email and Phone.

So it should like like this

    First Name    Last Name    Division     Email                  Phone
       Test          Guy         Exec     [email protected]        555-5555

Here is what I have, but it's not working:

    SELECT sr.LastName, sr.FirstName, dd.Name, 
        Email = (select sc.ContactValue FROM StaffContactInformation as sc
         INNER JOIN StaffRoster as roster on sc.StaffID = roster.ID
         where sc.ContactTypeID = 3 and roster.ID = sr.ID),
Phone = (SELECT sc1.ContactValue FROM StaffContactInformation as sc1 
        INNER JOIN StaffRoster as roster on sc1.StaffID = roster.ID
        where sc1.ContactTypeID = 1) 
    FROM StaffRoster as sr
    left join dictDivisions as dd on sr.DivisionID = dd.Id  
    left join StaffContactInformation as sci on sr.ID = sci.StaffID
    inner join dictStaffContactTypes as dsct on sci.ContactTypeID = dsct.ID 
    where (sr.Active = 1 and sr.isContractor = 0 )
    ORDER BY sr.LastName, sr.FirstName

I solved it with the following query:

    SELECT sr.LastName, sr.FirstName, dd.Name, 
        Email = (select sc.ContactValue FROM StaffContactInformation as sc
         INNER JOIN StaffRoster as roster on sc.StaffID = roster.ID
         where sc.ContactTypeID = 3 and roster.ID = sr.ID),
Phone = (SELECT sc.ContactValue FROM StaffContactInformation as sc 
        INNER JOIN StaffRoster as roster on sc.StaffID = roster.ID
        where sc.ContactTypeID = 1 and roster.ID = sr.ID) 
    FROM StaffRoster as sr
    left join dictDivisions as dd on sr.DivisionID = dd.Id 
    where (sr.Active = 1 and sr.isContractor = 0 )
    ORDER BY sr.LastName, sr.FirstName

Are they multiple duplicate rows? If so, just use SELECT DISTINCT? — Joe
– Joe, Commented Oct 21, 2013 at 16:08
Are all the values for each column for Mr Test Guy the same? Can you show us the result you are getting? — Andrew
– Andrew, Commented Oct 21, 2013 at 16:23
they are not duplicate rows, staff can have multiple contacts in contact table because there are multiple contact types, so they show up once for each contact type. I was trying to write this query to just show phone and email in the same row, so one row per staff — user1202606
– user1202606, Commented Oct 21, 2013 at 16:39

Michał Powaga · Accepted Answer · 2013-10-21 16:27:46Z

1

If you are selecting email and phone in subqueries these two joins are probably unnecessary:

left join StaffContactInformation as sci on sr.ID = sci.StaffID
inner join dictStaffContactTypes as dsct on sci.ContactTypeID = dsct.ID

Because of them you are getting as many rows as contacts for specific person.

Final query might look like:

SELECT sr.LastName, sr.FirstName, dd.Name, 
    Email = (
        select sc.ContactValue FROM StaffContactInformation as sc
        INNER JOIN StaffRoster as roster on sc.StaffID = roster.ID
        where sc.ContactTypeID = 3 and roster.ID = sr.ID
    ),
    Phone = (
        SELECT sc1.ContactValue FROM StaffContactInformation as sc1 
        INNER JOIN StaffRoster as roster on sc1.StaffID = roster.ID
        where sc1.ContactTypeID = 1
    ) 
FROM StaffRoster as sr
left join dictDivisions as dd on sr.DivisionID = dd.Id  
where (sr.Active = 1 and sr.isContractor = 0 )
ORDER BY sr.LastName, sr.FirstName

answered Oct 21, 2013 at 16:27

Michał Powaga

23.4k8 gold badges55 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1202606 Over a year ago

this put me in the right direction, I solved it by taking out the two joins you listed at the top, then I had to fix the subqueries. thanks

Moho · Accepted Answer · 2013-10-21 16:37:26Z

If you're running SQL Server 2005+, you can use a pivot table. Here I declare a CTE to encapsulate the pivot table with which you can join into the rest of your query:

;with ContactInfo( StaffId, Email, Phone )
as
(
    select
        sci.StaffID
        , [3] Email
        , [1] Phone
    from
        StaffContractInformation sci
        PIVOT( MAX(ContactValue) for ContactTypeId in ( [3], [1] ) PivotTable

)

SELECT 
    sr.LastName, 
    sr.FirstName, 
    dd.Name, 
    ci.Email,
    ci.Phone
FROM 
    StaffRoster as sr
    left join dictDivisions as dd 
     on sr.DivisionID = dd.Id  
    left join ContactInfo ci
     on sr.ID = ci.StaffID
  --  inner join dictStaffContactTypes as dsct 
     --on sci.ContactTypeID = dsct.ID 
where 
    sr.Active = 1 
    and sr.isContractor = 0
ORDER BY 
    sr.LastName, 
    sr.FirstName

Collectives™ on Stack Overflow

sql query get multiple values from same column for one row

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related