2 posts by 2 authors in: Forums > CMS Builder
Last Post: March 4   (RSS)

How do I get at all records from accounts for admin only reporting reporting? Does the Website Membership limit to only the owner's records?

Id like to Left Join two tables and do something like this on a webpage that loops through and formats the results if user is admin:

SELECT
cmsb_accounts.DistrictName,
cmsb_accounts.email,
cmsb_accounts.Address,
cmsb_accounts.City,
cmsb_accounts.State,
cmsb_accounts.Zip,
cmsb_accounts.County,
cmsb_accounts.Area,
cmsb_accounts.DistrictContact,
cmsb_accounts.Phone,
cmsb_accounts.Acres,
cmsb_accounts.Population,
cmsb_dataform.Biennium,
cmsb_dataform.RecordStatus,
cmsb_dataform.TotalFT,
cmsb_dataform.TotalPT,
cmsb_dataform.TotalTemp,
cmsb_dataform.OpCash,
cmsb_dataform.SpecUse,
cmsb_dataform.MillsRcv,
cmsb_dataform.MillsFullReq,
cmsb_dataform.MillsNum,
cmsb_dataform.AddlFunds,
cmsb_dataform.AddlFundsUse
FROM cmsb_accounts LEFT JOIN cmsb_dataform ON cmsb_accounts.createdByUserNum = cmsb_dataform.createdByUserNum
WHERE NOT cmsb_accounts.isAdmin OR cmsb_dataform.Biennium ="2019-2021"
ORDER BY cmsb_dataform.RecordStatus DESC,
cmsb_dataform.OpCash DESC,
cmsb_dataform.MillsRcv DESC,
cmsb_dataform.AddlFunds DESC,
cmsb_dataform.TotalFT ASC,
cmsb_dataform.TotalPT ASC,
cmsb_dataform.TotalTemp ASC;