How to make a left Join using a Where condition?

Question

I have two tables:

category
--------
id | nome | imagem

user_follow_category
--------------------
id | from | to | date

Note: the relationship of the two tables is given by category.id and by user_follow_category.to.

What I want to do is: select all categories (table category), but identify the ones being followed by user "x" in the table user_follow_category.

This is my query:

SELECT c.id, nome, image, u.id AS follow
FROM category AS c LEFT JOIN user_follow_category as u ON c.id = u.to
WHERE u.from = 74

Updating

table Category

id    nome       imagem (que ilustra a categoria na App)
1     futebol    futebol.jpg
2     tenis      tenis.jpg
3     box        box.jpg

table user_follow_category

id     from (id do usuário)     to (id da categoria)
1       74                         2
2       74                         1
3       62                         3

I want to get the following (what categories does user "74" follow?), something like:

id    nome     segue (ou alguma outra representação)
1     futebol    sim
2     tenis      sim
3     box        nao

Answer 1

Your problem is that the clause WHERE deletes lines that do not have the specified user. To resolve this, move the desired condition (user "x") to the clause ON:

SELECT c.id, nome, imagem, u.id AS follow
FROM category AS c LEFT JOIN user_follow_category as u 
    ON c.id = u.to AND u.`from` = 74;

Example in Sqlfiddle. Source: that answer in Soen.

This will return the id of the relation if any exists, or NULLif none exists. For a representation closer than you ask, you can use CASE to test the column u.id is void or not:

SELECT c.id, nome, imagem, 
    case when isnull(u.id) then "não" else "sim" end AS follow
FROM category AS c LEFT JOIN user_follow_category as u 
    ON c.id = u.to AND u.`from` = 74;

Updated example.

Answer 2

Try the query below:

SELECT c.id, c.nome, c.image, u.id AS follow, IF(u.id, 'Sim', 'Não') as segue FROM category c 
LEFT JOIN (user_follow_category u) ON (c.id = u.to)
WHERE u.from = 74

Note that it may be c.image as your table, because I followed the fields according to your query.

Answer 3

I believe this is what you need:

Select * from category a,user_follow_category b where a.id=b.to