CODEWITHSUNDEEP
mysqljoin
Maarten Hartman
Maarten Hartman

Reputation: 1629

mysql join tables, query returns only rows with matching rows

Currently I have this mysql :

$result = mysql_query("SELECT blog.id, blog_comments.cid, blog.name,
  blog.blogtext, blog.date, blog.like, blog.dislike, blog_comments.comment, blog_comments.date
FROM blog, blog_comments  
WHERE blog.id = blog_comments.cid   
ORDER BY blog.id DESC LIMIT 30")

The query returns rows and their comments, the problem is: it ONLY returns rows with their comments, it doesn't return rows WITHOUT comments. I've found a few topics, and discovered that I probably should use FULL OUTER JOIN. Now I've tried to apply this, absence success.

Assistance is much appreciated

Upvotes: 2

Views: 1528

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270637

Rather than an implicit join (comma-separated list of tables produces an INNER JOIN), use an explicit LEFT JOIN:

SELECT 
  blog.id,
  blog_comments.cid,
  blog.name,
  blog.blogtext,
  blog.date, 
  blog.like, 
  blog.dislike, 
  blog_comments.comment, 
  blog_comments.date
FROM 
 blog
 /* LEFT JOIN will return rows from blog (the "left" side of the join) even if no 
    related rows exist in blog_comments (the "right" side of the join) */
 LEFT JOIN blog_comments ON blog.id = blog_comments.cid   
ORDER BY blog.id DESC
LIMIT 30

Upvotes: 3

Related Questions