CODEWITHSUNDEEP
mysqljoininner-join
Sebastian Farham
Sebastian Farham

Reputation: 825

How to display data from database when JOIN table is empty?

This question has been bothering me ever since I started self-learning. Say I have this query: 

SELECT * FROM ve_articles a 
JOIN ve_articles_categories c ON a.CategoryId=c.id 
JOIN ve_articles_comments ac ON a.id=ac.ArticleId WHERE a.UserId='$id'

This query will return an empty results set since there are no comments for the given article. But there is an article. So how can I still join the third table ve_articles_comments with the ve_articles and display the articles even if there are no data in ve_articles_comments?

Upvotes: 0

Views: 63

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30819

You can use LEFT JOIN instead of JOIN. Here's MySQL's documentation, this is what it says:

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. You can use this fact to find rows in a table that have no counterpart in another table.

Try the below query:

SELECT * FROM ve_articles a 
LEFT JOIN ve_articles_categories c ON a.CategoryId=c.id 
LEFT JOIN ve_articles_comments ac ON a.id=ac.ArticleId 
WHERE a.UserId='$id';

Upvotes: 1

Related Questions