MySQL join query help

Question

Basically I have a mysql query something like the following:

mysql_query("SELECT n.title, v.value FROM posts n INNER JOIN votes v ON n.id = v.id");

And what I need to do is to grab the title from the posts table and the current vote value from the votes table.

At the moment, votes aren't stored into the votes table unless a vote happens. So all posts with 0 votes aren't in the votes table.

This causes an error that it's trying to get the title AND vote value where the post id = the vote value id. If there isn't any record in the votes table, then value should return as NULL or 0 but at the moment the whole query returns as null so I can't even return the title.

How can I fix this all in one query? I don't want to have to use multiple queries... Thanks :)

Answer 1

Use a left join instead of an inner

An inner join requires matching rows from the table on both sides of the join.

A left join (or left outer join to use its full name) selects all rows from the left table, and then matching rows from the right, returning null values if there are no matching rows in the right table, just like you are asking for.

(There are also right outer joins, but the same effect can be achieved by changing the conditions around in the ON clause)

Answer 2

SELECT n.title, v.value FROM posts n LEFT JOIN votes v ON n.id = v.id

Answer 3

SELECT n.title, v.value FROM posts as n, votes as v WHERE n.id = v.id

Try that.