james Oduro james Oduro - 3 months ago 8
MySQL Question

Select all from a T1 and select three culumn from T2 whose id column is the same in T1 user_id column

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:

<?php

$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'];
}
}

<?


database structure

Please any help will be much appreciated.
Thank you.

Answer

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

SELECT
  u.*, f.* 
FROM
  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

and

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

Comments