SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Never used a UNION or a JOIN, but I need to

    I'm using WordPress. I want to select a group of id's in one table, and use those id's to select a bunch of posts in another table, and only retrieve the most recent post.

    So, theoretically, I want to do this:

    $article_ids_query = mysql_query("SELECT `object_id` FROM `wp_term_relationships` WHERE `term_taxonomy_id`='" . $term_taxonomy_id[0] . "'"); //this will retrieve a bunch of `object_id's`

    $articles = mysql_query("SELECT `post_title`, `post_content` FROM `wp_posts` WHERE `ID`='" . $article_ids_query . "' LIMIT 1 ORDER BY `post_date` ASC");


    The obvious problem is that the $article_ids_query is going to be a result set.

    How could I do it so that I use the query in $article_ids_query to be part of the $articles query.

    This is what I could come up with using a "UNION", but I've never used UNION before:

    (SELECT `object_id` as `a` FROM `wp_term_relationships` WHERE `term_taxonomy_id`='" . $term_taxonomy_id[0] . "') UNION (SELECT `post_content`, `post_title` FROM `wp_posts` WHERE `ID`=`a` AND `post_type`='post') LIMIT 1 ORDER BY `post_date` ASC

    Of course, I appreciate any and all help. Josh

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT p.post_title
         , p.post_content 
         , p.post_date
      FROM wp_term_relationships AS r
    INNER
      JOIN ( SELECT id
                  , MIN(post_date) AS first_post
               FROM wp_posts
             GROUP
                 BY id ) AS m
        ON m.id = r.object_id
    INNER
      JOIN wp_posts AS p
        ON p.id =  m.id
       AND p.post_date = m.first_post
     WHERE r.term_taxonomy_id = " . $term_taxonomy_id[0]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    Josh, maybe use a plugin (http://wordpress.org/extend/plugins/...osts-plugin/)? I thought I had another plugin installed but guess not.
    Ryan B | My Blog | Twitter

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT p.post_title
         , p.post_content 
         , p.post_date
      FROM wp_term_relationships AS r
    INNER
      JOIN ( SELECT id
                  , MIN(post_date) AS first_post
               FROM wp_posts
             GROUP
                 BY id ) AS m
        ON m.id = r.object_id
    INNER
      JOIN wp_posts AS p
        ON p.id =  m.id
       AND p.post_date = m.first_post
     WHERE r.term_taxonomy_id = " . $term_taxonomy_id[0]
    WOW. I don't know how many yrs experience you have doing this stuff, but that query worked beautifully and I am impressed at it. Thank you.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jlipinski3 View Post
    WOW.
    thanks

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •