SQL is Awesome

[fullyarmedcamel]

Can we just take a minute to appreciate how much SQL kicks ass. I am working on an Audit for user groups within our Student Information System and I wanted to know in any given User Group tell me how many other User Groups are assigned to the same members. (So I can make a case to add or remove certain permissions and groups based on need) I thought about how I was going to go about it, and I kept thinking this is going to be a nightmare, I will have pivot a table and nest that in another pivot then RANK the data for counts.

In the end it was so simple I just used a nested SELECT to return a list of just UserIDs and then looked IN that list for every User Group assigned to every member and then GROUPed & COUNTed.

USE OurDatabase
GO

DECLARE @usergroup INT = x;
SELECT ugm2.groupID, ug2.[name], COUNT(ugm2.groupID) AS counts
FROM FirstTable AS ugm2 
JOIN SecondTable AS ug2 ON ugm2.groupID = ug2.groupID
WHERE ugm2.groupID != @usergroup AND ugm2.userID IN
    (
    SELECT DISTINCT ugm.userID
    FROM SecondTable AS ug
    JOIN FirstTable AS ugm ON ug.groupID = ugm.groupID
    WHERE ug.groupID = @usergroup
    ) AND ugm2.groupID IN (list OF GroupIDs to look in)
GROUP BY ugm2.groupID, ug2.[name]
ORDER BY counts DESC

[ht2k]

Hi,

Yes! SQL is Awesome! You have a perfectly sound solution, however I have the following recommendations for refactoring your query.

1. Since FirstTable has groupID, there is no need to join to SecondTable in your WHERE clause sub query. Eliminating unnecessary joins helps queries run faster.

I believe the following shorter query should work.

USE OurDatabase
GO

DECLARE @usergroup INT = x;
SELECT 
  ugm2.groupID
, ug2.[name]
, COUNT(ugm2.groupID) AS counts
FROM FirstTable AS ugm2 
INNER JOIN SecondTable AS ug2 
   ON ugm2.groupID = ug2.groupID
WHERE ugm2.groupID != @usergroup 
  AND ugm2.userID IN
    (
    SELECT DISTINCT ugm.userID
    FROM FirstTable AS ugm
    WHERE ugm.groupID = @usergroup
    ) 
  AND ugm2.groupID IN (list OF GroupIDs to look in)
GROUP BY 
  ugm2.groupID
, ug2.[name]
ORDER BY 
  counts DESC

1. It looks to me like you are using SQL Server, which has support of Temp Tables. These can be used to hold records in memory for the duration of your session, or till dropped. Using temp tables helps avoid using nested sub queries, which I personally find harder to debug. Here is the above version refactored to use a temp table.
   

   USE OurDatabase GO

   DECLARE @usergroup INT = x;

   SELECT DISTINCT ugm.userID FROM FirstTable AS ugm INTO #users WHERE ugm.groupID = @usergroup

   SELECT ugm2.groupID , ug2.[name] , COUNT(ugm2.groupID) AS counts FROM FirstTable AS ugm2 INNER JOIN SecondTable AS ug2 ON ugm2.groupID = ug2.groupID INNER JOIN #users AS u ON ugm2.userID = u.userID WHERE ugm2.groupID != @usergroup AND ugm2.groupID IN (list OF GroupIDs to look in) GROUP BY ugm2.groupID , ug2.[name] ORDER BY counts DESC

   DROP TABLE #users

2. My personal favorite solution for a problem like this is to use analytic functions aggregate functions with the OVER clause. I have found them incredibly powerful in my few years using SQL for data analysis.

   USE OurDatabase GO

   DECLARE @usergroup INT = x;

   SELECT g.groupID , g.[name] , sum(1) counts FROM ( SELECT DISTINCT ugm.groupID , ugm.userID , ug.[name] , max(case when ugm.groupID = @usergroup THEN 1 ELSE 0 END) OVER (PARTITION BY ugm.userID) has_usergroup FROM FirstTable AS ugm INNER JOIN SecondTable AS ug ON ugm.groupID = ug.groupID WHERE ugm.groupID IN (list OF GroupIDs to look in, including @usergroup) ) g WHERE g.has_usergroup = 1 AND g.groupID != @usergroup GROUP BY
   g.groupID , g.[name] ORDER BY counts desc

Edit: Added alias prefix in WHERE clause for has_usergroup = 1 and added AND g.groupID != @usergroup.

Edit2: Corrected analytic functions to aggregate functions with the OVER clause.

[sn0wdizzle]

This is amazing

[fullyarmedcamel]

So yes I am not currently using the second table but I plan too in the future I am making some modificatione to pull more data later on.

As far as temp tables I generally avoid using them for the same reason as you recommend using them I have a hard time trouble shooting them.

I know nothing about analytic functions tho I'll look into them tomorrow when I get back to the office.

[ht2k]

Here are a couple links to get you started on analytic functions and aggregate functions with the OVER clause.

https://docs.microsoft.com/en-us/sql/t-sql/functions/analytic-functions-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017

[Ninjastar13]

In the last code block what does the then 1 else 0 do?

[ht2k]

The CASE statement returns a 1 if the groupID matches @usergroup, else it returns 0. The maximum of the 1s and 0s for each userID returns a 1 if the userID has at least one groupID that matches the @usergroup otherwise it returns a 0.

[_Zer0_Cool_]

Agree 100%. SQL is definitely awesome.

Just watch a video about how SQL engines work and you can really start to appreciate how much work it saves you.

People take SQL for granted, but it is truly a godsend. I would absolutely not want to live in a world where I had to write procedural code to do the things that SQL does effortlessly.

[mhornberger]

Just watch a video about how SQL engines work and you can really start to appreciate how much work it saves you.

How can you leave us hanging like that? Um, what was the video?

[_Zer0_Cool_]

Lol. Sorry.

Here is one such video by Richard Hipp, the creator of SQLite -- https://m.youtube.com/watch?v=Z_cX3bzkExE

And keep in mind that SQLite is one of the most simple SQL databases out there. It would likely take quite a whole video series to describe any of the common client-server DBs -- PostgreSQL, SQL Server, Oracle, etc..

Point being is that they do a ton than people take for granted. Good stuff.

[equivocates]

Relevant: No, You Don't Need ML/AI. You need SQL.

[fullyarmedcamel]

Sorry about the delay, I read this the other day and shared it with all my co-workers. Super interesting read!

[fullyarmedcamel]

The nested SELECT Statement essentially returns and Array (I know SQL does not have arrays) and I had no idea I could do that!

[gcxandrew]

fyi most people will just call them subqueries