I am having trouble with a query that returns data for a staff contact list on a DNN reports page. The query that works fine is below:
SELECT Cast(HR_master.GivenName+''+HR_master.Surname as varchar(50)) as Name, PhoneNumber1 as Phone, PhoneNumber4 as Extension, PhoneNumber3 as Cellular FROM HR_Master Where HR_master.EmployStatus = '01'
The problem is that in the PhoneNumber3 field, we have some work cell numbers and some personal cell numbers. We only want the work ones to show, so we enter each personal one in the DB like this: 555-1212(personal)
I need to filter those to only display work ones and leave the personal ones blank (not showing).
I thought I could do it with a sub query. Here is what I tried.
SELECT Cast(HR_master.GivenName+''+HR_master.Surname as varchar(50)) as Name, PhoneNumber1 as Phone, PhoneNumber4 as Extension, (SELECT PhoneNumber3 FROM HR_master WHERE PhoneNumber3 not like '%(personal)') AS Cellular FROM HR_Master Where HR_master.EmployStatus = '01'
But I get an error that the query returned more than 1 result. Can anyone see how I could restructure this for the correct result? I have not been able to find an example of a sub query that does this kind of thing.
THX!