Condition (WHERE) with IF

Asked

Viewed 132 times

0

I have two tables (posts and posts_promoted). Both relate and the search "normal" works smoothly. However, I need to do a search to return all posts and, if the post belongs to posts_promoted, bring me only posts_promoted with status = "N".

I tried something like

SELECT * FROM posts AS POST [...] WHERE [..] AND IF((SELECT COUNT(*) FROM posts_promovidos WHERE posts_promovidos.post_id = post.id) > 0,posts_promovidos.ativo = "N","")

However it did not work as wanted because it is only bringing the promoted posts and ignoring the posts "not promoted" (that do not appear in the posts_promoted table).

What would be the most correct way to do this search?

EDIT - SOLUTION

I decided as follows (sharing to help anyone who has something similar):

SELECT * FROM posts AS POST [...] WHERE [..] AND posts.id NOT IN(select post_id from posts_promovidos) OR posts_promovidos.ativo = "N"
mysql

2 answers

0

LEFT JOIN solves your problem:

SELECT
   p.*
FROM posts p
LEFT JOIN posts_promovidos pp
    ON p.id = pp.id
    AND pp.status = 'N'

  • I already have JOIN properly configured and working. I only have this question of, in some cases, check if the post is on posts_promoted and, if it is, that it is not active (active = "N"). JOIN is unconditional, ie,.

0


select * from posts_promovidos where posts_promovidos.status = "N"
inner join (select * from posts where posts.id not in posts_promovidos) p on p.id = posts_promovidos.id
  • 1

    Luis, welcome to Stackoverflow! It would be interesting if you added a short explanation of how it answers the question

Browser other questions tagged mysql

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