SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Location
    London
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Can someone help with a join SQL statement

    Hi

    I've got a Wordpress powered site but I've also got a few php pages outside of my WP installation which I need to query the database that powers my WP site. Everything is fine apart from the fact that I don't really know how to write a particular SQL statement which involves a join. I was wondering if someone could help me.

    Basically what I want is to be able to get the ID, post_title, post_excerpt, post_content from the wp_posts table and then the meta_value from the table wp_postmeta table but where the meta_key = "large_download_button" and the ID from wp_posts matches the post_id on wp_postmeta

    Of course wp_posts.ID and wp_postmeta.post_id are the common fields between the two tables.



    These are the two tables wp_posts and wp_postmeta

    wp_posts
    ID
    post_author
    post_date
    post_date_gmt
    post_content
    post_title
    post_excerpt
    post_status
    comment_status
    pint_status
    post_password
    post_name
    to_ping
    pinged
    post_modified
    post_modified_gmt
    post_content_filtered
    post_parent
    guid
    menu_order
    post_type
    post_mine_type
    comment_count

    wp_postmeta
    meta_id
    post_id
    meta_key
    meta_value

    many thanks for your help

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by juanc View Post
    ... get the ID, post_title, post_excerpt, post_content from the wp_posts table and then the meta_value from the table wp_postmeta table but where the meta_key = "large_download_button" and the ID from wp_posts matches the post_id on wp_postmeta
    let's see how closely the SQL solution matches this requirement statement

    Code:
    -- ID, post_title, post_excerpt, post_content from wp_posts 
    SELECT post.ID
         , post.post_title
         , post.post_excerpt
         , post.post_content 
    -- meta_value from wp_postmeta
         , meta.meta_value
      FROM wp_posts AS post
    INNER
      JOIN wp_postmeta AS meta
    -- the ID from wp_posts matches the post_id on wp_postmeta
        ON meta.post_id = post.id
    -- where the meta_key = "large_download_button" 
       AND meta.meta_key = 'large_download_button'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Location
    London
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello thank you very much that worked! I really appreciate your help.


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
  •