sql query if a value is 0

Question

Here is my query:

SELECT f.id AS id, 
       c.city AS city,
       ca.category AS cat 
FROM following AS f 
     JOIN cities AS c ON f.city=c.id 
     JOIN categories AS ca ON f.category=ca.id 
WHERE f.member = $this->id

The problem is sometimes city or cat can equal 0. If they equal zero, I can't have them join the cities or categories table, as there isn't a value in the table for 0, nor do I want one.

So essentially I want the above query but I want: if city!=0 join the cities table, and if cat!=0 join the categories table.

I don't mind whether I change the PHP or the SQL, but I just can't work out how this can be achieved.

Answer 1

Try to use LEFT JOIN in your query

Answer 2

The correct answer is based on the expected output.

If you do not want entries from the following table in the scenario that city or category is zero, then you need not bother at all. As you are using INNER JOINS, they fetch data from the following table only if a record with given city ID or category ID is found.

However, if your requirement is that you need to show empty value when city or category value tends to 0, then the LEFT JOIN shall do the trick.

SELECT f.id AS id, 
  IF(c.city IS NULL, 'NO CITY', c.city) AS city,
  IF(ca.category IS NULL, 'NO CATEGORY', ca.category) AS cat 
FROM following AS f 
LEFT JOIN cities AS c ON (f.city=c.id) 
LEFT JOIN categories AS ca ON (f.category=ca.id)
WHERE f.member = $this->id

Answer 3

Use if condition to check the value of city and cat before applying join condition:

SELECT f.id AS id, c.city AS city,ca.category AS cat 
FROM following AS f 
JOIN cities AS c ON if(f.city!=0,f.city=c.id,0=1)
JOIN categories AS ca ON if(ca.id!=0,f.category=ca.id,0=1)