This query helps you obtain a listing of customers with all the system details and the site telephone number.
USE MANITOU Select c.CONTID , co.FULLNAME , a.ADDR1 --, a.ADDR2 , ci.NAME as City , r.NAME as State , a.POSTCODE , st.DESCR as AcctType , sy.SYSNO , sy.PANELTYPE , t.RLDES , t.TXID , t.LASTSIGNAL , ph.CONTPOINT as SitePhone from CUSTOMER c join CONTACT co on co.SERIALNO=c.SERIALNO and co.CONTTYPE=1 join MONITORING m on m.SERIALNO=c.SERIALNO and m.COMMSTATE = 1 left join CONTPOINT ph on ph.SERIALNO=c.SERIALNO and ph.CONTTYPE=1 and ph.CPTYPENO=0 join CUSTPRIV p on p.SERIALNO=c.SERIALNO join SUBTYPES st on st.SUBTYPE=p.SUBTYPE and st.TYPE = 1 join ADDRESS a on a.SERIALNO=c.SERIALNO join CITY ci on ci.CITYNO=a.CITYNO join REGION r on r.REGIONNO=a.REGIONNO join STX t on t.SERIALNO=c.SERIALNO join CUSTSYS sy on sy.SYSNO=t.SYSNO and sy.SERIALNO=c.SERIALNO order by st.DESCR, c.CONTID
