james Oduro james Oduro - 2 months ago 4x
MySQL Question

How to JOIN 2 tables on mySql?

I have two tables: publick_feed and users

I want to SELECT all from public_feed and also SELECT a three columns from users whose id is the same of user_id in public_feed
and assign the rows returned from public_feed to the column in users table ( correspondent)

I try this:


$sql = "
SELECT * FROM public_feed
WHERE user_id IN
(SELECT id FROM users) AND
(SELECT Firstname,Lastname,Avatar FROM users WHERE id IN(SELECT user_id FROM public_feed))


$query = mysqli_query($dbc_conn,$sql);
if(mysqli_num_rows($query) > 0){
while($row = mysqli_fetch_assoc($query)){
//echo rows with correspondent details from the users table
echo $row['user_id'];


Please any help will be much appreciated.
Thank you.


Or version with left join in case if there is no user in public_feed, and you still want to fetch user data

  u.*, f.* 
  public_feed f LEFT JOIN
  users u ON f.user_id = u.id;

Because author asked for explanation, here it is:

First we are going to use table name alias to make query shorter

public_feed f


users u

we are saying that want to refer to tables with an alias. Of course * means that we want to select all columns

SELECT users.*, public_feed.*

is equal to

SELECT u.*, f.*

Of course you can use any other letters as an alias

Next we are saying that public_feed.user_id must be equal to users.id. But when public feed entry does not exists just display columns with null values. This is why we are using LEFT JOIN instead of INNER JOIN. In general JOINS are used to fetch related data from more than one related tables.

ON keyword is saying values from which columns in the tables must be equal to satisfy the request