Unable to write a proper query to join two tables and fetch what I need

Question

I'm trying to fetch data from table1 which doesn't have a column with a specific value I include

Consider a social media site: I have a "posts" table where I save all the posts by the user with their User id, and I have the "follow" table where I save all the data like who's following who.

Now I'm trying to get all the data from the posts table where the user isn't following them

Example:

posts table

| u_id | Post |
|:---- |:----:|
| 1    |post1 |
| 2    |post2 |
| 1    |post3 |
| 3    |post4 |

follow table:

| u_id | following |
|:---- |:---------:|
| 2    | 1         |
| 1    | 3         |

Now the scenario: Let's say I'm the logged-in user with user id: 2, as per the requirement I should only see the posts of users that I'm not following, i.e., user #2 (which is me) and user #3 only.

So far the query I tried is:

$query = "SELECT posts.id, posts.u_id, posts.title, posts.date 
FROM posts 
LEFT JOIN follows 
ON posts.u_id=follows.u_id 
WHERE (follows.u_id != '$u_id')";

The $u_id is the logged-in user's id which is in the above scenario #2.

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Jul 29 '21 at 11:39

score 3 · Answer 1 · answered Jul 29 '21 at 11:33

3

Now I m trying to get all the data from the posts table where the user isn't following them

SELECT p.id, p.u_id, p.title, p.date
FROM posts p LEFT JOIN
     follows f
     ON p.u_id = f.following AND
        f.u_id = ?
WHERE u.u_id IS NULL;

LEFT JOIN is a fine approach. However, this is looking at what posts match following not u_id. Then you want to return the rows where there are no matches.

answered Jul 29 '21 at 11:33

Gordon Linoff

1,242,037
58
646
786

Thanks for the answer, and few more doubts.. 1. How do I add to make sure I see my posts as well i.e., the "posts" of user_id #2 2. How do I see the posts of users who are not listed in the "follows" table i.e., the user who has a post but follows or followed by none. – Ambiguous Turtle Jul 29 '21 at 11:42
@Vdofyvideos . . . This answers the question that you asked here (at least I think it does). If you have a further question, then ask a *new* question. – Gordon Linoff Jul 29 '21 at 12:05

score 2 · Accepted Answer · answered Jul 29 '21 at 11:48

You ask to find posts of users which you are not following. Use NOT IN, NOT EXISTS, or LEFT JOIN:

SELECT *
  FROM posts
 WHERE posts.u_id NOT IN (SELECT following FROM follows WHERE u_id = 'me')
   AND posts.u_id <> 'me'  -- Remove this line if you want to see your posts.
;

I assume you wouldn't follow yourself. Just remove the noted line in the WHERE clause to see your posts.

This is similar to the LEFT JOIN approach.

Unable to write a proper query to join two tables and fetch what I need

2 Answers2