I am a consultant new to this client and new to Sybase. I am working in the telecommunications industry and a client wants to know an active subscriber count in a particular group of counties, broken down by county. Then they wanted to narrow it further by only having a certain Billing Code. Now they want a list of clients from this existing report that don't have phone service. The problem is, if I understand it correctly, there are multiple records for each subscriber, when the digital_phone_service field is brought into the query. There are two potential values in that column, a 0, meaning the client doesn't have digital_phone_service and a one when they do. I basically want to select only one of the records with a zero in the digital_phone_service field. I have played with this a bit and I am pretty sure a subquery is in order here and I have played with variations on this ( including distinct ), but I am not getting the desired results ). Can someone please assist here? Thanks so much.
select cnt_county_name, SBP_SUBSCRIBER_NUMBER, sbs_bill_code, sbs_service_code, srm_digital_phone_service
from dbo.SUBSCRIBER_PACKAGE s, county, subscriber_service, service_ext_matrix
where county.CNT_SYS_PRIN_ID = sbp_sys_prin_id
and county.CNT_AGENT_ID = sbp_agent_id
and s.SBP_SUBSCRIBER_NUMBER = sbs_subscriber_number
and s.SBP_SYS_PRIN_ID = sbs_sys_prin_id
and s.SBP_AGENT_ID = sbs_agent_id
and SBS_SYS_PRIN_ID = srm_sys_prin_id
and SBS_AGENT_ID = srm_agent_id
and subscriber_service.SBS_SERVICE_EXT_CODE = srm_service_ext_code
and SBP_SYS_PRIN_ID = '82231500'
and subscriber_service.SBS_BILL_CODE = 'K5'
--and service_ext_matrix.SRM_DIGITAL_PHONE_SERVICE = 0
and s.SBP_SUBSCRIBER_NUMBER = '8223150250000459'
--group by cnt_county_name
--order by cnt_county_name