u.user_wvid AS UserID,
LOWER( u.login ) AS Login,
LOWER( u.password ) AS Password,
u.subname AS SubName,
s.full_name AS FullName,
COUNT( DISTINCT pa.Trade ) AS TradeCount
COUNT( DISTINCT t.onsite <>0 ) AS OnSiteCount, -- PROBLEM IS RIGHT HERE
MAX( a.timestamp ) AS LastActiveStamp,
IFNULL( DATE_FORMAT( a.timestamp, '%a. %b. %D, %Y' ) , '-' ) AS LastActiveDisp
FROM wv_user u
CROSS JOIN wssubc s ON u.subname = s.subname
CROSS JOIN SubProjectAccess pa ON u.subname = pa.SubName
CROSS JOIN wstrades t ON pa.Trade = t.tcode
LEFT JOIN user_activity a ON u.user_wvid = a.user_wvid
WHERE u.role_wvid =2
GROUP BY UserID
Basically I'm selecting a user summary from five tables. The problem lies in I'm trying to figure out if each user is considered onsite, offsite, or both. The tables to determine this are the SubProjectAccess and wstrades, which are linked on a trade code. wstrades has an onsite column with a 1 if the trade is onsite and 0 if it is offsite. SubProjectAccess lists which trades each user is linked to.
I have already figured out how to determine the number of trades each user has access to. What I'd like to add is a count of how many of those trades are onsite, which would enable me to determine which category the user falls into.
I'm hoping someone with more MySQL knowledge is out there!