How to SELECT SQL data using the results from a previous SELECT-CodePudding

Lets guess I have 3 tables:

Table a where I have a name and email

Table b where I have a text and user (as a reference to email from table a)

Table c where I have follower and following (both references to email in table a)

Im trying to develop a simple html/php/sql web that allows me to register many users and let them post different texts while also having the chance to follow or be followed by other users (already done) and I want to give an user the possibility to display the texts from table b that he himself posted and those from the users he is following

Im seriously struggling with how to extract this information

SELECT b.text
  FROM tableB as b
 LEFT JOIN tableC as c
    ON b.user = c.follower
 WHERE b.user = "currentuser"

This is as far as I got, which only shows the texts posted by the user himself (something I can do way more simple) but I cant seem to understand how to get those from the users he is following

I hope its understandable without any photo

CodePudding user response：

You first want to find all following users in table c rows where the current user is the follower. Then you want to add the current user (or alternatively always have all users follow themselves). Then you want to find all texts for those users.

So:

select b.text
from (
    select following as user
    from tableC
    where follower="current user"
    union
    select "current user"
) show_users
join tableB as b on b.user=show_users.user

or if you have a tableC row where follower=following for all users, just:

select b.text
from tableC as c
join tableB as b on b.user=c.following
where c.follower="current user"