Problem with LEFT JOINs joins and a condition [closed]

1

I have had this problem for some time now. I am counting on your help.

I have 3 tables:

publicacao
id  cartaz          titulo
1   eradogelo.jpg   A Era do gelo
2   ojogo.jpg       O Jogo
3   helpme.jpg      Help Me
4   please.jpg      Please
5   6words.jpg      6 Words

Stores the reader and favorite post (liked):

favorito
id  from(id do usuário)  to(id da publicação)
1   74                   2
2   74                   3

Stores the reader and the publication it has read:

leu
id  from(id do usuário) to(id da publicação)
1   74                   2
2   74                   3
3   74                   5

Now, select all posts and tell me (tick) which user (74) favored and which one he read.

Query:

SELECT
    p.id,
    p.cartaz,
    p.titulo,
    case when isnull (f.id) then 'não' else 'sim' end AS favorito,
    case when isnull (l.id) then 'não' else 'sim' end AS leu
FROM publicar AS p
LEFT JOIN favoritos as f ON p.id = f.to
LEFT JOIN leu as leu ON p.id = l.to AND f.'from' = 74
ORDER BY p.id

Result:

id(da publicação) cartaz          titulo         favorito  leu
1                 eradogelo.jpg   A Era do gelo  não       não
2                 ojogo.jpg       O Jogo         sim       sim
3                 helpme.jpg      Help Me        sim       sim
4                 please.jpg      Please         não       não
5                 6words.jpg      6 Words        não       não (deveria ser "sim")

If it read and did not favor (liked) both columns comes with the "no". I'd like you to help with this, please.

    
asked by anonymous 27.01.2015 / 20:20

1 answer

3

The filter per user must be in both:

LEFT JOIN favoritos as f ON p.id = f.to AND f.from = 74
LEFT JOIN leu as leu ON p.id = l.to AND l.'from' = 74
    
28.01.2015 / 03:38