How to Retrieve the Following List of Users Who Are Not Already Followed by the Current User? SQL Relationships
Suppose you have two tables in your database: users and fans. The users table looks like this:
| id | name |
|---|---|
| 1 | John |
| 2 | Mark |
| 3 | Bill |
| 4 | Steve |
| 5 | Jeff |
And the fans table looks like this:
| fan | user |
|---|---|
| 1 | 2 |
| 1 | 5 |
| 2 | 3 |
| 2 | 4 |
| 2 | 5 |
In this case, fan is the user who is following, and user is the one being followed.
Now, if we take John (id 1) as the current user, and John is following Mark (id 2), the question is: How can we retrieve the list of people that John is following through the people that John follows (in this case, Mark), but exclude those John is already following?
In this example, Mark is following Bill (id 3), Steve (id 4), and Jeff (id 5). However, John is already following Jeff, so the result should only include Bill (id 3) and Steve (id 4).
I have tried using the IN operator, but I am not sure if this would scale well with a large number of entries. Could anyone help me solve this?
I tried to create a table and sample data based on whatever info available in your question and ran 2nd query in the question. Then I modified join condition and group by. db-fiddle
last join condition in 2nd query to add BaseType, BaseLine and DocEntry
LEFT JOIN
ign1 t5 ON t5.”ItemCode” = t1.”ItemCode” AND t5.”BaseType” = ’67’ AND t5.”BaseEntry” = t1.”DocEntry” AND t5.”BaseLine” = t1.”LineNum”
update group by statement to include DocEntry
t2.”ItemCode”, t2.”ItemName”, t1.”Whscode”, t4.”ItmsGrpNam”, t5.”DocEntry”, t3.”U_Batch”, t3.”LotNumber”, t3.”ExpDate”
Please correct me know in case I failed to understand of your question in first place.