SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    Bristol ,England
    Posts
    177
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Too many AND conditions

    Hi trying to run a query on a Wordpress database and my current query is returning an empty set when it should be returning at least 1 result, could this be because of too many "AND' conditions?

    Code MySQL:
    SELECT * FROM wp_posts, wp_postmeta WHERE wp_posts.ID = wp_postmeta.post_id AND wp_postmeta.meta_key='main_video' AND wp_postmeta.meta_value=1 AND wp_postmeta.meta_key= 'main_video' AND wp_postmeta.meta_value= 'Alexander Armstrong'

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,039
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    There is no such thing as "too many AND conditions".
    If there is a row in your database that adheres to all criteria you should get it back, if there are no such row(s) you won't get it back. It's as simple as that.
    Without the actual data I'm afraid we can't help you any further though.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    everybody would be better off, jamessy, if you would adopt some minimal formatting conventions for the sake of human beings, not system software

    i mean, mysql has no trouble handling line breaks and extra spacing, so what's holding you back?



    okay, here's your query, reformatted --
    Code:
    SELECT * 
      FROM wp_posts
         , wp_postmeta 
     WHERE wp_posts.ID = wp_postmeta.post_id
    
       AND wp_postmeta.meta_key = 'main_video'
       AND wp_postmeta.meta_value = 1
    
       AND wp_postmeta.meta_key = 'main_video'
       AND wp_postmeta.meta_value = 'Alexander Armstrong'
    rémon, can you see what's wrong with this now?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    Bristol ,England
    Posts
    177
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I agree, just got caught up in "copy and paste" moment. Sorry, just corrected the query and issue still stands.

    Code MySQL:
    SELECT * 
     FROM wp_posts
        , wp_postmeta 
    WHERE wp_posts.ID = wp_postmeta.post_id
     
      AND wp_postmeta.meta_key = 'main_video'
      AND wp_postmeta.meta_value = 1
     
      AND wp_postmeta.meta_key = 'artiste'
      AND wp_postmeta.meta_value = 'Alexander Armstrong'

  5. #5
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,039
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    rémon, can you see what's wrong with this now?
    I do, yeah ...

    jamessy, a row can't have two different values at the same time. The field wp_postmeta.meta_key either has the value 'main_video', or it has the value 'artiste', but it can't have both values at once.

    It seems that what you're looking for is something like

    Code mysql:
    SELECT
       something
     , anything
     , just_not_the_dreaded_star
    FROM
      wp_posts AS wp_p
    INNER JOIN
      wp_postmeta AS wp_pm1
      ON
      wp_p.ID=wp_pm1.post_id
    INNER JOIN
      wp_postmeta AS wp_pm2
      ON
      wp_p.ID=wp_pm2.post_id
    WHERE wp_pm1.meta_key = 'main_video'
      AND wp_pm1.meta_value = 1
     
      AND wp_pm2.meta_key = 'artiste'
      AND wp_pm2.meta_value = 'Alexander Armstrong'

    although I'm sure Rudy knows a better way to write that
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  6. #6
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    Bristol ,England
    Posts
    177
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh right, so I need to create an alias. Thanks for that.

    The worst thing is that I have had Rudy's book open in front of me for the last 2 hours! feeling a little bit ashamed, especially with the formatting!

  7. #7
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,039
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    It's not about the alias per se, it's about using wp_postmeta twice in the same query
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  8. #8
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    Bristol ,England
    Posts
    177
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got you. Works perfectly by the way, thanks again!

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    geez, i go to the beer store and you guys figure it out nicely while i'm gone

    here's another way --
    Code:
    SELECT wp_posts.* 
      FROM wp_posts
    INNER
      JOIN ( SELECT post_id
               FROM wp_postmeta 
              WHERE meta_key = 'main_video' AND meta_value = 1
                 OR meta_key = 'artiste' AND meta_value = 'Alexander Armstrong'
             GROUP
                 BY post_id
             HAVING COUNT(*) = 2 ) AS b
        ON b.post_id = wp_posts.ID
    if you would like to discuss the benefits of this method over the multi join, let me know
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Creative solution, Rudy! I'm diggin it!

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    Creative solution, Rudy! I'm diggin it!
    thanks

    so that was the general form for "must have both of these 2 conditions"

    can you see what it would be for "must have at least 2 of these 3 conditions"?

    rémon, can you imagine what the join solution would look like for 2 of 3?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,039
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by r937 View Post
    rémon, can you imagine what the join solution would look like for 2 of 3?
    Yup, it gets messy real fast. Your way looks a lot better (provided that the meta_key,meta_value pair are unique of course -- which I assume they are) and is probably more efficient as well because you just need one join instead of multiple.
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  13. #13
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    probably more efficient as well because you just need one join instead of multiple.
    Not only that... the aggregate comparison in the subquery reduces the set to only the matching IDs, so your join is hashing an order of magnitude less comparisons, and doing so on ID-to-ID (very fast). It's a very efficient solution.


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
  •