How to make a left join using a where condition?

Question

How to make a left join using a where condition?

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

5

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 user_follow_category.to .

What I want is to: select all categories (table category ), but identify the ones that are being followed by user "x" in 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

Update

table category

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

user_follow_category table

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 "74" follow?), something like:

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

mysql sql select

asked by anonymous 27.01.2015 / 10:13

3 answers

1

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, as I followed the fields according to your query.

27.01.2015 / 11:41

0

I believe this is what you need:

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

27.01.2015 / 10:40

Help - C # how to update comboBox? autocomplete type URLs in doctype and html tags

score 2 · Accepted Answer

Your problem is that the WHERE clause deletes rows that do not have the specified user. To resolve this, move the desired condition (user "x") to the 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: this answer in SOen .

This will return the relationship id if any exists, or NULL if none exist. For a closer representation of what you ask, you can use CASE to test whether the u.id column is null 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 .