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!
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
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?
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.