Condition (WHERE) with IF

Navigation
0

I have two tables (posts and posts_promovidos). Both are related and the "normal" search works without problems. However, I need to do a search that returns all posts and, if the post belongs to posts_promovidos, bring me only the posts_promovidos 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","")

But it did not work the way it wanted because it is only bringing the promoted posts and ignoring the "not promoted" posts (not included in the posts_promovidos table).

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

EDIT - SOLUTION

I solved it 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"
    
asked by anonymous 28.01.2015 / 16:05

2 answers

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
    
28.01.2015 / 16:13
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'
    
28.01.2015 / 16:13
Inserting records into the database only after you have finished inserting into a List Problems with routes in ASP.NET MVC