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...)

Comments

Post a Comment

Popular posts from this blog

OverTheWire[.com] Natas Walkthrough - JUST HINT, NO SPOILERS

game portal: http://overthewire.org/wargames/natas/ PASSWORDS for each level are stored in /etc/natas_webpass/natasX I made this walkthrough for people like me, i needed some help, but didnt want the spoiler, so here i will give you all the information needed to pass each level, yet not the solution. For levels needed custom web request i made a different post for powershell and javascript  with how-to's, since is a piece of learning for itself, and also for those of us that play at work and have only powershell at their hands. Although its not a complete spoiler there, its quite most of the solution so try yourself 1st. Natas The Natas game is from basic to advances web hacking. Every few levels is about whole new exploitation (with some harder ones doing comeback later), so a lot of learning. If you're new, you're the reason i am writing so much even for the 1st level, just please google EVERY topic you see, since in the following levels i assume you know the ...
Read more

Asp.Net Ending Response options, Response.End() vs CompleteRequest()

this blog is for any1 who get the ThreadAbortException when caling Response.End(), and then tried the other options such as HttpContext.Current.ApplicationInstance.CompleteRequest() and found out that there was no replacement. i'll start with the bottom line: if you want to stop right now your response and send it as is you dont have any other option other than Response.End(), period and it's the right thing to throw that EX in that case. the answer is its because there is the "HTTP pipeline" and the "Asp.Net pipeline", and CompleteRequest() cuts the HTTP pipeline but not the Asp.Net one so everything that is from handlers forwars (including all page events) continues but your part, the asp one, stays in your hands so that no information will be lost, so ur suppose to manage your code currectly, since information lost is really a bad thing, although in most cases we use Response.End() we know what we are losing and we want it. see this short article ab...
Read more

SPFx with Angular, Full tutorial

 White you can use the pnp-generator , I really wanted to created this kind of solution all by myself, since I also wanted to implement it with SP-2019, with SFPx 1.4.x Part 1: Setup I started with a clean machine, installed LTS node 14.17.0 , and installed latest gulp, yo, sp-generator for yo, and angular-cli command: npm install gulp yo @microsoft/generator-sharepoint --global results: + gulp@4.0.2 + yo@4.2.0 + @microsoft/generator-sharepoint@1.12.1 command: npm install -g @angular/cli results: + @angular/cli@12.0.2 *NOTE - To the time of writing, I had an error generating browser application bundles so I downgraded to angular 10.x, and re-create the project npm uninstall -g @angular/cli npm cache clean --force npm install -g @angular/cli@10.2.0 And so our journey begins! Create new Ng project ng new ng-for-spfx-ex Create new SPFx WebPart Project - just  yo Part 2: Test NG with SPFx Within the  angular.json   file lets change the output path, I've made 1 folde...
Read more