MySQL / PHP：如果value不为0，则使用inner join

Question

I want to use a select query which gets data from tables.

It looks like this

SELECT table1.value1, table1.value2, table1.id, table2.id, table2.value2
FROM table1
INNER JOIN table2
ON table2.id = table1.value4
ORDER BY table1.id DESC
LIMIT 10

Though table1.value4 can sometimes be 0 and there are no table2.id with the value 0 and since it's a auto-increment value, it starts at 1. And I want it to start at 1.

Because when it's equal to 0 then that specific row isn't available, just the other ones.

But I would like to some how set a where clause that it only should get the table2 values if table1.value4 isn't equal to 0.

Answer 1

What you actually want, it seems, is a LEFT JOIN then. All rows from table1 will be returned, even if there is no match in table2 (as with table1.value4 = 0).

SELECT table1.value1, table1.value2, table1.id, table2.id, table2.value2
FROM
  table1
  LEFT JOIN table2
    ON table2.id = table1.value4
ORDER BY table1.id DESC
LIMIT 10