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,
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
FROM ewConsumerProfiles ewC
--Get people who are following me
LEFT JOIN (
SELECT cp.ConsumerProfileID AS FollowerConsumerProfileID ,
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 ,
WHERE FollowingConsumerProfileID = @CounsumerProfileID
) c3 ON c2.FollowerConsumerProfileID = c3.ConsumerProfileID
WHERE ewC.ConsumerProfileID = @CounsumerProfileID;