CODEWITHSUNDEEP
mysql
alexfsk
alexfsk

Reputation: 29

SQL Query JOIN with 4 tables

Who can help me?

I have 4 tables posts, users, users details and comments.

Here is the strucutre:

posts

users

users_details

comments

I have a query which i want to display comments on specific post.

This is what i written but avatar column it doesn't show any results. It seems i written it wrong on join or something.

SELECT comments.id as comment_id,
    comments.user_id as user_id,
    comments.post_id as post_id,
    comments.parent as parent,
    comments.comment as comment,
    comments.posted as posted,
    users_details.avatar as avatar,
    users.username as username
FROM `comments`
    LEFT JOIN users ON users.id = comments.user_id
    LEFT JOIN users_details ON users_details.user_id = users.id
    WHERE comments.post_id='60337'
ORDER BY posted ASC

Here is a screenshot of the table with results https://i.sstatic.net/QZxjT.png

I want to mention the avatar field it has a value for that user in users_details table so it should be displayed...

Thanks!

Upvotes: 1

Views: 62

Answers (1)

Eray Balkanli
Eray Balkanli

Reputation: 7960

Two potential reasons I could tell causing this issue.

1. The users do not have an avatar.

Try: Select Avatar from Users_Details where User_Id in (2,9212) and make sure it returns some data.

2. The Primary/Foreign key being used for joining the tables is not matching

Run those queries below and ensure that they are returning the same users.

select * from users where id in (2,9212) 
select * from users_details where user_id in (2,2912)

Upvotes: 2

Related Questions