Using two AND conditionals dependent on eachother in a query?

Databases
1

I wasn’t sure how best to phrase the title and I’m normally intuitive enough to figure these things out but I’m stumped by this problem so I’ll explain it. This is my query:

SELECT posts.*,
(SELECT COUNT(flagger_member_id) FROM flags WHERE flagged_post_id = posts.id) AS flag_count

FROM posts WHERE post_type NOT LIKE 1

AND ( (author_ip NOT IN ( ‘188.28.3.230’ ) AND author_id = 0 )

ORDER BY posts.id DESC LIMIT 0 , 8

The bold AND query is where I’m having trouble (it’s obviously incorrect) but my idea is for the query to return all rows where the author_ip is not in the specified ranges (this works elegantly by itself), but also for that conditional to ONLY happen when the author_id is 0 (one conditional relying on the other).

So for example:

author_ip -> 188.28.3.230 : author_id -> 1 = would be returned
author_ip -> 217.20.9.130 : author_id -> 1 = would be returned
author_ip -> 188.28.3.230 : author_id -> 0 = would not be returned

Is there any way to write this query to perform the way I want it to? Is there perhaps something I’m not seeing here? I’d appreciate any help I can get… I’m not an expert on MYSQL, just a hobbyist so if there’s something I can learn about where I’m screwing up, I’d be glad for the pointers!

2

How come you’re using NOT IN () instead of <> (not equal)? Do you have more than one value in that column?

3

The query is dependent on other queries which determine the IP ranges, so yes there can be many IPs (in this example there was just one).

4

Thanks for the clarification.

It seems like you have an extra paren in that AND clause. See if it works without it (the formatting is just for my readability).


SELECT  posts.*
	,(
	SELECT COUNT(flagger_member_id) 
	FROM flags 
	WHERE flagged_post_id = posts.id
	) AS flag_count
FROM posts 
WHERE post_type <> 1
AND ( 
      author_ip NOT IN ('188.28.3.230') 
      AND author_id = 0 
	)
ORDER BY posts.id DESC LIMIT 0 , 8
5

Thanks for the quick reply.

It doesn’t work because that last AND conditional (AND author_id = 0) is affecting the entire query and only returning rows where the author_id is 0.

I want all rows to be returned regardless of what the author_id is, except those where both the author_ip is in the NOT IN ranges AND the author_id is 0. (dependent on eachother)

It’s actually pretty hard to describe. Perhaps this calls for a more complex set of queries?

6

Ah… so we have it backwards then, because we’re selecting records where both the author_up is not in the range and the author_id is 0.

Can you try reversing that AND statement?


AND ( 
      author_ip IN ('188.28.3.230') 
      AND author_id <> 0 
	)
7

Ahh got it. You got me to finally wrap my head around that one! Was also a problem of exclusion, so I added one more conditional (an OR) to negate the first one. This returns the correct set of records. This final query works:

SELECT posts . * , (

SELECT COUNT( flagger_member_id )
FROM flags
WHERE flagged_post_id = posts.id
) AS flag_count
FROM posts
WHERE post_type NOT LIKE 1
AND (

(author_ip IN (‘188.28.3.230’) AND author_id <> 0)
OR (author_ip NOT IN (‘188.28.3.230’))

)

ORDER BY posts.id DESC
LIMIT 0 , 8

I would have never for the life of me figured this out on my own. Thank you so much for taking the time! It’s much appreciated.

8

Excellent! Sometimes you just need a second set of eyes.

9 

 where post_type <> 1
   and not ( author_ip in ('188.28.3.230') 
         and author_id = 0
           )

:slight_smile:

10

Figures that Rudy would have some magic sql guru way to do it! :stuck_out_tongue:

Seriously though, I didn’t know you could use a NOT outside of the paren like that. stores in memory