CODEWITHSUNDEEP
phpmysqljoin
Oskar Persson
Oskar Persson

Reputation: 6755

MySQL / PHP: Use inner join if value isn't 0

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.

Upvotes: 0

Views: 1063

Answers (2)

Sashi Kant
Sashi Kant

Reputation: 13465

TRy this::

If you dont have 0 in table2 id, the below query works fine::

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

But there are some values in table2 having id 0 and you want that to be ignored then 

SELECT table1.value1, table1.value2, table1.id, table2.id, table2.value2
FROM table1
INNER JOIN table2
ON (table2.id = table1.value4 and table1.id!=0)
ORDER BY table1.id DESC
LIMIT 10

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270637

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

Upvotes: 3

Related Questions