I have a select that is working but would like to know if it is efficient.
Potentially looking at @ 300k records
There is a base table
There is a table of people that are folliing other people.
I can be both an object and the subject of following or being followed.
Using id from my profile "ewC"
Get the folks that are following me "c2"
Get the folks that I am following "c3"
Set to 1 or 0 in my case statement
DECLARE @CounsumerProfileID INT;SET @CounsumerProfileID = 35557;SELECT ewC.ConsumerProfileID MyID, ewC.UserID MyUserID, ewC.FirstName , ewC.LastName , c2.FollowerConsumerProfileID FollowingMeID, c2.FirstName + ' ' + c2.LastName FollowingMeName , c2.AvatarImage FollowingMeAvatar , --If I am following the person that is following me CASE WHEN c3.ConsumerProfileID IS NULL THEN 0 ELSE 1 END I_AM_FollowingFROM ewConsumerProfiles ewC --Get people who are following me LEFT JOIN ( SELECT cp.ConsumerProfileID AS FollowerConsumerProfileID , cp.FirstName , cp.LastName , cp.AvatarImage , cf.ConsumerProfileID FROM ewConsumerFollowing cf INNER JOIN ewConsumerProfiles cp ON cp.ConsumerProfileID = cf.FollowingConsumerProfileID ) c2 ON ewC.ConsumerProfileID = c2.ConsumerProfileID --Get the people I am following LEFT JOIN ( SELECT ConsumerProfileID , FollowingConsumerProfileID FROM ewConsumerFollowing WHERE FollowingConsumerProfileID = @CounsumerProfileID ) c3 ON c2.FollowerConsumerProfileID = c3.ConsumerProfileIDWHERE ewC.ConsumerProfileID = @CounsumerProfileID;
A unique ID
A person being followed id
And the person following them id
It is set in a community page with a simple checkbox
A person can follow me...
However it happens... I can follow them as well
There are now two records
And the to personal ids that reference each other
I don't design it myself