Problem with LEFT Joins joins and one condition

Asked

Viewed 84 times

1

I have had this problem for some time. I count 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 the 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 publication he 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 (check) which user (74) you preferred and which one you 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

Upshot:

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 he read and did not favorite(like) both columns come with "no". I would like you to help in this, please.

sql query join
  • 1

    Put the text in place of the image and also describe the expected problem/result.

  • The question is: when the user reads a post and does not like (or does not put to his favorites) the answer whether or not the post is always equal to the answer of the favorite. As it is in the table above.Please review the tables.

  • 1

    Has a mistake leu as leu ON p.id = l.to should be leu as l ON p.id = l.to. If you want to use an alias you have to define it, as is the l is nothing. Besides said try not to give names of fields equal to the table name.

  • I got a little distracted. It’s done. Thanks @Jorge B!

1 answer

3


Filter per user needs to 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
  • 1

    has yet another problem, leu as leu should be leu as l.

  • Thanks @luciorubeens, it’s working with this tip. Vlw dude.

Browser other questions tagged sql query join

You are not signed in. Login or sign up in order to post.