SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Using two AND conditionals dependent on eachother in a query?

    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. #2
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    How come you're using NOT IN () instead of <> (not equal)? Do you have more than one value in that column?
    <cfset myblog = "http://cydewaze.org/">

  3. #3
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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).

    Code SQL:
    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
    <cfset myblog = "http://cydewaze.org/">

  5. #5
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Veros12 View Post
    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)
    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?

    Code SQL:
    AND ( 
          author_ip IN ('188.28.3.230') 
          AND author_id <> 0 
    	)
    <cfset myblog = "http://cydewaze.org/">

  7. #7
    SitePoint Member
    Join Date
    Dec 2011
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Excellent! Sometimes you just need a second set of eyes.
    <cfset myblog = "http://cydewaze.org/">

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
     where post_type <> 1
       and not ( author_ip in ('188.28.3.230') 
             and author_id = 0
               )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Figures that Rudy would have some magic sql guru way to do it!

    Seriously though, I didn't know you could use a NOT outside of the paren like that. *stores in memory*
    <cfset myblog = "http://cydewaze.org/">


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •