sql statement sorting from other table with NOT IN

Question

I am designing a simple microblog website for a class and need some help with an sql statement for my scrolling pagination.

I have 2 tables: User and Follows.

In my 'User' table I have uid, FirstName, LastName, Email, and Username.

I have another 'Follows' table which has rid, FollowedName, and FollowingName.

For example, if username "Alex" followed username "Bob" then a new row would exist with FollowedName="Bob" and FollowingName="Alex".

For a search page, I allow the user to sort based on popularity (which is based on the number of followers). So I need an SQL statement which will select all of the rows from 'User' and then order them based on how many entries in the 'Follows' table each user has.

On top of this, I need the statement to filter out those users whose uid is in a string name $explodedids.

I have everything working but I cannot figure out where to put the "WHERE uid NOT IN (".$explodedids.")".

Here is my statement which returns properly but does not filter out $explodedids:

"SELECT Username, FirstName, LastName, Email, COUNT( Followingname ) AS count 
 FROM (SELECT u.Username, u.FirstName, u.LastName, u.Email, f.Followingname 
       FROM User AS u LEFT JOIN Follows AS f ON u.Username = f.Followingname) 
 AS T GROUP BY Username ORDER BY count DESC LIMIT ".$postnumbers

$postnumbers is simply the limit number on my scrolling pagination. I am sure that I have just been putting the WHERE NOT IN in the wrong place but if you guys could help me out that would be awesome.

score 1 · Answer 1 · edited Dec 10 '13 at 00:00

"SELECT Username, FirstName, LastName, Email, 
 COUNT( Followingname ) AS count 
 FROM (
 SELECT u.Username, u.FirstName, u.LastName, u.Email, f.Followingname 
       FROM User AS u 
       LEFT JOIN Follows AS f 
       ON u.Username = f.Followingname 
       WHERE u.id NOT IN($explodids)
 ) AS T 
 GROUP BY Username 
 ORDER BY count DESC 
 LIMIT $postnumbers";

Assuming that $explodids is a string in format 'x,y,z'

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

Here is a select statement that will meet your requirements:

--Test Data
CREATE TABLE #User (  uid int
                   , FirstName varchar(50)
                   , LastName varchar(50)
                   , Email varchar(50)
                   , Username  varchar(50));

INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(1,'MackF','MackL','mack@mack.com','MackUname');
INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(2,'2F','2L','2@mack.com','2Uname');
INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(3,'3F','3L','3@mack.com','3Uname');
INSERT INTO #User(uid, FirstName, LastName, Email, Username) VALUES(4,'4F','4L','4@mack.com','4Uname');

CREATE TABLE #Follows( rid int
                    , FollowedName varchar(50)
                    , FollowingName varchar(50));

INSERT INTO #Follows VALUES(1,'4Uname','2Uname');
INSERT INTO #Follows VALUES(2,'MackUname','4Uname');
INSERT INTO #Follows VALUES(3,'4Uname','3Uname');
INSERT INTO #Follows VALUES(4,'MackUname','3Uname');

--Query
SELECT u.Username
     , u.FirstName
     , u.LastName
     , u.Email
     , COUNT( f.Followingname ) AS followercount 
FROM #User u 
LEFT JOIN #Follows f 
   ON u.Username = f.Followingname
WHERE uid NOT IN (2,4)--here is where your WHERE clause goes remove this line to see all records
GROUP BY u.Username
ORDER BY followercount DESC

--Results
USERNAME    FIRSTNAME   LASTNAME    EMAIL           FOLLOWERCOUNT
3Uname      3F          3L          3@mack.com      2
MackUname   MackF       MackL       mack@mack.com   0

Here is a SQL fiddle to try it all out.

NB: I have renamed your column "count" to "followercount" as COUNT is a reserved word.

This is close but it seems to not return all of the elements. Would this work if a user had zero followers? — user3060454, Dec 09 '13 at 23:16
Check out this SQL fiddle: http://sqlfiddle.com/#!2/083f8/11 it shows an example of just that...I think the reason that not all elements are returned may be the WHERE uid NOT IN(2,4) — Mack, Dec 09 '13 at 23:22
I'm an idiot I had miscounted the number of users it was working all along. Thanks for the help! — user3060454, Dec 10 '13 at 00:04
You're welcome, feel free to pay it forward, but I disagree with the first part of your comment - an idiot would have struggled along in silence...anyone can make a mistake and it takes a good person to admit it. Happy coding! — Mack, Dec 10 '13 at 00:19

sql statement sorting from other table with NOT IN

2 Answers2