Manitou Core - SQL Query - SELECT ACCOUNT BY AUTHORITY

01/03/2024 10:57 am EST
/****** SELECT ACCOUNT BY AUTHORITY ******/

SELECT
CU.[CONTID] AS 'CUSTOMER ID'
, AU.[CONTID] AS 'AUTHORITY ID'
, CO.[FULLNAME] AS 'AUTHORITY NAME'
, AD.[ADDR1]
, AD.[ADDR2]
, AD.[ADDR3]
, AD.[ADDR4]
, CI.[NAME] AS 'CITY'
, RE.[SHORTNAME] AS 'COUNTRY/STATE'
, AD.[POSTCODE]

FROM [MANITOU].[dbo].[CUSTOMER] CU
JOIN [MANITOU].[dbo].[AUTHORITY] AU ON AU.[SERIALNO] IN (CU.[POLICENO],CU.[FIRENO],CU.[MEDICALNO])
JOIN [MANITOU].[dbo].[CONTACT] CO ON CO.[SERIALNO] = AU.[SERIALNO]
JOIN [MANITOU].[dbo].[ADDRESS] AD ON AD.[SERIALNO] = CU.[SERIALNO] AND AD.[CONTTYPE] = 1
JOIN [MANITOU].[dbo].[CITY] CI ON CI.[CITYNO] = AD.[CITYNO]
JOIN [MANITOU].[dbo].[REGION] RE ON RE.[REGIONNO] = AD.[REGIONNO]

https://boldwiki.boldgroup.com/link/134#bkmrk-whereco.%5Bconttype%5D-%3D
  
WHERE
CO.[CONTTYPE] = 5
AND                                                            -- FOR ALL ADD /* BEFORE THE AND AND */ AFTER THE )
AU.[CONTID] IN ('P005','F005')                   -- ENTER IN THE CORRECT AUTHORITY ID(S) NEEDED

ORDER BY
CU.[CONTID] ASC
, AU.[CONTID] ASC
Was this article helpful?