Multi table query mysql subquery

[OsaMasw]

Hello buddies , I have no Idea what is subquery, every time I tried to link two tables in one sql query I failed
here is the scenario I want:
in this pic I have posts with "user_id" only I need to show the username which stored on another tables called users
I've tried using but its not working.

$sq= "SELECT user_id, username FROM users WHERE user_id IN (SELECT user_id FROM posts WHERE user_id= '".$id."')";

posts table


this is the users tables

[blocblue]

You need to use a join. If you only want results where they appear in both tables, you need an inner join.

I'm not sure why you need to query both tables when you're passing the user id to the query. You could simply select the data directly from the users table using the id.

However, please find an example join query below:

SELECT `u`.`id`, `u`.`username`
FROM `users` `u`
INNER JOIN `posts` `p` ON (`p`.`user_id` = `u`.`id`)
WHERE `u`.`id` = 22

[OsaMasw]

Thanks for reply, I need to learn how to join two tables, especially if I have "like" table and "comments" table, so I asked about subquery , anyway thanks for help.

[simplypixie]

If you really want to use sub-queries, and presuming you want to show all posts by each user, then this is the sort of thing you will need (I don't know how you want to layout on the page or what your columns are named in your table so have guessed and you will have to adjust accordingly)

$sq= mysql_query("SELECT user_id, username FROM users");

while ($user = mysql_fetch_array($sq)) {
    $sq2 = mysql_query("SELECT * FROM posts WHERE user_id='".$user['user_id']."'");

    echo '<p>'.$user['username'].'</p>';

    while ($posts = mysql_fetch_array($sq2)) {
        echo '<p>'.$posts['title'].'</p>';
        echo '<p>'.$posts['content'].'</p>';
    }
}

If you want to only pull users that actually have posts then combine what you already have with what I have put

$sq= mysql_query("SELECT user_id, username FROM users WHERE user_id IN (SELECT user_id FROM posts)");

[blocblue]

@simplypixie - why would you use N + 1 queries to find all users and their posts, rather than use a single query?

SELECT p.*, u.username
FROM posts p
INNER JOIN users u ON (u.id = p.user_id)

[simplypixie]

@blocblue - I wouldn't but the OP wanted to know how to do sub-queries as hasn't learnt about joins yet and I am just trying to help them do what they want to do at the moment (espeically if they can't do a sub-query yet then they are really going to struggle with joins).

[OsaMasw]

Thanks for the simplify the operation Mr. @simplypixie, its really helpful to understand how subquery works, I'll leave ths question open if anyone need help in this subject.

[simplypixie]

Glad I could help, but please mark as Solved.