May 27, 2013 at 6:47 pm
greetings all,
I'm trying to return a row of data for each licence and want that row to include the phone number columns for all numbers associated with that row using joins. However I seem to be getting a row per phone number. I have to use left joins as a phone number and email address might not exist for that licence. See query below and then a sample of the result. I'd appreciate some help. Thanks.
Query:
SELECT
distinct licdet.LicNum
,licdet.CustomerId
,licdet.customerholder
,licdet.issuedate
,licdet.expirydate
,case licdet.LicStatusDesc when 'current' then 'C' else 'E' end as LicStatusDesc
,licdet.HolderAddress1
,licdet.HolderAddress2
,licdet.holdersuburb
,licdet.HolderState
,licdet.HolderPostcode
,CASE CONTACT_USAGE_ID WHEN '54076' THEN tel.formatted_number else '' END AS business
,CASE CONTACT_USAGE_ID WHEN '54075' THEN tel.formatted_number else '' END AS Mobile
,CASE CONTACT_USAGE_ID WHEN '54078' THEN tel.formatted_number else '' END AS Fax
,case CONTACT_USAGE_ID WHEN '54081' Then inter.ADDRESS else '' end as Internet
FROM [Navigate38].[lt_dba].[dfv_pr_lic_detail] licdet
inner join [Navigate38].[lt_dba].[LS_CLIENT] cli on licdet.CustomerId = cli.CLIENT_REF and licdet.LicStatusDesc <> 'cancelled'
inner join [Navigate38].[lt_dba].[LS_CONTACT_METHOD] conmet on cli.CLIENT_ID = conmet.CLIENT_ID
and CONTACT_USAGE_ID in (54076, 54075, 54078, 54081)
left join [Navigate38].[lt_dba].[LS_TELEPHONE] tel on conmet.telephone_id = tel.TELEPHONE_ID
left join [Navigate38].[lt_dba].[LS_INTERNET] inter on conmet.INTERNET_ID = inter.INTERNET_ID
order by licdet.licnum
result example:
LicNumCustomerIdcustomerholderissuedateexpirydateLicStatusDescHolderAddress1HolderAddress2holdersuburbHolderStateHolderPostcodebusinessMobileFaxInternet
CBL10133489MALEMBO PTY LTD2013-01-01 00:00:002013-12-31 00:00:00CGRD FLOOR, 14 FORESHORE DRIVEGERALDTONWA6530PLOW@WN.COM.AU
CBL10133489MALEMBO PTY LTD2013-01-01 00:00:002013-12-31 00:00:00CGRD FLOOR, 14 FORESHORE DRIVEGERALDTONWA653008 9921 8121
CBL10133489MALEMBO PTY LTD2013-01-01 00:00:002013-12-31 00:00:00CGRD FLOOR, 14 FORESHORE DRIVEGERALDTONWA65300418 647 750
CBL10133489MALEMBO PTY LTD2013-01-01 00:00:002013-12-31 00:00:00CGRD FLOOR, 14 FORESHORE DRIVEGERALDTONWA653008 9921 1221
May 27, 2013 at 8:53 pm
Try using an aggregate such as the below:
selectlicdet.LicNum,
licdet.CustomerId,
licdet.customerholder,
licdet.issuedate,
licdet.expirydate,
case licdet.LicStatusDesc when 'current' then 'C' else 'E' end as LicStatusDesc,
licdet.HolderAddress1,
licdet.HolderAddress2,
licdet.holdersuburb,
licdet.HolderState,
licdet.HolderPostcode,
max ( case CONTACT_USAGE_ID when '54076' then tel.formatted_number else '' end ) as business,
max ( case CONTACT_USAGE_ID when '54075' then tel.formatted_number else '' end ) as Mobile,
max ( case CONTACT_USAGE_ID when '54078' then tel.formatted_number else '' end ) as Fax,
max ( case CONTACT_USAGE_ID when '54081' then inter.ADDRESS else '' end ) as Internet
from[Navigate38].[lt_dba].[dfv_pr_lic_detail] licdet
inner join[Navigate38].[lt_dba].[LS_CLIENT] cli on licdet.CustomerId = cli.CLIENT_REF and licdet.LicStatusDesc <> 'cancelled'
inner join[Navigate38].[lt_dba].[LS_CONTACT_METHOD] conmet on cli.CLIENT_ID = conmet.CLIENT_ID and CONTACT_USAGE_ID in ( 54076, 54075, 54078, 54081 )
left join[Navigate38].[lt_dba].[LS_TELEPHONE] tel on conmet.telephone_id = tel.TELEPHONE_ID
left join[Navigate38].[lt_dba].[LS_INTERNET] inter on conmet.INTERNET_ID = inter.INTERNET_ID
group by
licdet.LicNum,
licdet.CustomerId,
licdet.customerholder,
licdet.issuedate,
licdet.expirydate,
licdet.LicStatusDesc,
licdet.HolderAddress1,
licdet.HolderAddress2,
licdet.holdersuburb,
licdet.HolderState,
licdet.HolderPostcode
order by licdet.licnum
May 27, 2013 at 10:39 pm
Many thanks Mansfield. That seems to have doen the trick. I had started down that road, but obviously I didn't specify my grouping correctly, so removed it again.
Your help is much appreciated.
regards,
Rob
May 27, 2013 at 10:47 pm
You're welcome 🙂
The rule of thumb with GROUP BY is to include every column in the result set that isn't wrapped inside an aggregate function. For computed expressions eg. the CASE statement - strip out the logic and include only the referenced column(s).
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply