Monday, August 20, 2012

Join and Count from 1 table

what i initally wanted to do is that
SELECT  _users.ID,
        _users.FullName AS 'Name',
        MAX(_users_logins.LastLogin )AS 'Last Login',
        COUNT(_users_companies.Company_ID) AS 'No. of Costumers',
        COUNT(_users_companies.Company_ID) AS 'No. of companies in talk'
but u cant do it with joins purly cuz it will count the result
so my good Chief Moshe tought me this
SELECT _users.ID,
       _users.FullName AS 'Name',
       _LOGINS.LastLogin AS 'Last Login',
       _COMP.costumers AS 'No. of Costumers',
       _TALKS.TALKS AS 'No. of companies in talk'
FROM _users
LEFT JOIN (SELECT MAX( representative_logins.Login_Datetime ) AS LastLogin,
           _users_logins.Rep_ID
           FROM  _users_logins
           GROUP BY Rep_ID) AS REP_LOGINS
    ON _users.ID=REP_LOGINS.Rep_ID
LEFT JOIN (SELECT COUNT(Company_ID) AS costumers ,UserID
           FROM _users_companies
           GROUP BY UserID) AS REP_COMP
    ON REP_COMP.UserID=_users.ID
LEFT JOIN (SELECT COUNT(CompanyID) AS TALKS ,UserID
           FROM conversation
           GROUP BY UserID) AS REP_TALKS
    ON REP_TALKS.UserID=_users.ID
the secret is that since the selects arn't sub-selects but alias tables you dont get into nested selects but instead its like 'select 1, (another query with 1 result), 2' (or someting like that...)

No comments:

Post a Comment