SitePoint Sponsor

User Tag List

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

    Tying 4 tables together using a Join

    Basic SQL queries are no problem for me, but the Joins and complex ORDER BY isn't easy for me.

    There's 4 tables, and they are part of a Wordpress setup.

    What I'm trying to do is summed up in 4 SELECT statements:

    1. SELECT guid FROM wp_posts WHERE id=(JOIN category_inclusions) AS main_query ORDER BY (JOIN lineup) ASC

    2. SELECT term_id FROM wp_terms WHERE slug='home-slideshow-mouseovers' AS category_ids

    3. SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id=(JOIN category_ids) AS category_inclusions

    4. SELECT meta_value FROM wp_postmeta WHERE metakey='slot' AND post_id='(JOIN category_inclusions)' AS lineup

    The summation of it is this:

    QUERY1's IDs match where=QUERY3's category_ids match where QUERY2's rows include "home-slideshow-mouseovers", and sort it all according to what "slot" value QUERY1's IDs have in the "wp_postmeta" table.

    It's complicated, but seems perfect for some good JOIN syntax. I've played w/different query creators, but it's a tough task even knowing what should take priority.

  2. #2
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    To simplify this up a bit & just to get me some traction on this, say I removed a condition.

    I'm just looking for the JOIN syntax to do this:

    SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id=(SELECT term_id FROM wp_terms WHERE slug='home-slideshow-mouseovers') AS category_inclusions

    And then to ORDER BY, then the category_inclusions have to be JOIN'ed somehow.

    ORDER BY (SELECT meta_value FROM wp_postmeta WHERE metakey='slot' AND post_id='(JOIN category_inclusions)' AS lineup

    When I look at different examples, the queries get tricky for me when "ON" is used. The masterminds of SQL would think this is cake, the same as how I could point out the best practices to use w/PHP, so the help is appreciated.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jlipinski3 View Post
    The masterminds of SQL would think this is cake...
    nope

    the reason your first post went unanswered, and the second one is likely to have the same result, is because it's not at all clear what you're trying to do

    nor did you explain what the tables contain, or how they are related
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937. I'm trying to explain this the best way that I can, so here's putting it in more conversational English.

    There's 4 tables:

    "wp_posts" contains rows of content.
    "wp_terms" is a table of categories.
    "wp_term_relationships" ties categories by their id in wp_terms to their object_id in wp_posts.
    "wp_postmeta" is a table containing custom fields and their values, tied to a record in wp_posts by id.

    "wp_posts"
    id, content

    "wp_terms"
    id, category_name

    "wp_term_relationships" ties posts to categories
    object_id (essentially wp_posts.id), category_id (essentially wp_terms.id)

    "wp_postmeta"
    custom_fieldname, custom_fieldvalue, content_id (essentially wp_posts.id)



    So what I'm trying to do is tie everything together, and putting it in simple english, "I'm trying to select all posts of a certain category and order them according to a custom field called 'slot'."

    Which would come up like this:

    SELECT id, content FROM wp_posts WHERE id=(JOIN: SELECT object_id's FROM wp_term_relationships WHERE category_id=(JOIN: SELECT id's FROM wp_terms WHERE category_name="home")) ORDER BY (JOIN SELECT wp_postmeta.fieldvalue WHERE wp_postmeta.content_id=wp_posts.id AND wp_postmeta.fieldname="slot")

    It just looks like a mess when it's typed out like that.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jlipinski3 View Post
    "I'm trying to select all posts of a certain category and order them according to a custom field called 'slot'."
    i can do the first part for you...
    Code:
    SELECT wp_posts.id
         , wp_posts.content
      FROM wp_terms
    INNER
      JOIN wp_term_relationships
        ON wp_term_relationships.category_id = wp_terms.id
    INNER
      JOIN wp_posts
        ON wp_posts.id = wp_term_relationships.object_id
     WHERE wp_terms.category_name = 'home'
    think you can add the remaining table?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,147
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    (untested)

    Code SQL:
    SELECT
          p.id
          ,p.content
      FROM
          wp_posts p
     INNER
      JOIN
          wp_term_relationships t2p
        ON
          p.id = t2p.object_id
     INNER
      JOIN
          wp_terms t
        ON
          t2p.category_id = t.id
      LEFT OUTER
      JOIN
          wp_postmeta m
        ON
         p.id = m.content_id
       AND
         m.custom_fieldname = 'slot'
     WHERE
          t.category_name = 'home'
     ORDER
        BY
          m.custom_fieldvalue

  7. #7
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i can do the first part for you...
    Code:
    SELECT wp_posts.id
         , wp_posts.content
      FROM wp_terms
    INNER
      JOIN wp_term_relationships
        ON wp_term_relationships.category_id = wp_terms.id
    INNER
      JOIN wp_posts
        ON wp_posts.id = wp_term_relationships.object_id
     WHERE wp_terms.category_name = 'home'
    think you can add the remaining table?
    Thanks r937, so far it's working great. I'm playing around with where to drop the final sorting, and what I've come up with


    SELECT wp_posts.id, wp_posts.content
    FROM wp_terms
    INNER
    JOIN wp_term_relationships
    ON wp_term_relationships.category_id = wp_terms.id
    INNER
    JOIN wp_posts
    ON wp_posts.id = wp_term_relationships.object_id
    WHERE wp_terms.category_name = 'home'
    INNER
    JOIN wp_postmeta
    ON wp_postmeta.content_id = wp_posts.ID WHERE wp_postmeta.custom_fieldname = 'slot'
    ORDER BY wp_postmeta.custom_fieldvalue ASC


    Swapping that final "WHERE" with "AND" doesn't do the trick either. What would I be better off doing to rethink the sorting logic?

  8. #8
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    r937, nevermind, I GOT IT!

    SELECT
    wp_posts.ID,
    wp_posts.post_content,
    wp_postmeta.meta_value
    FROM
    wp_terms
    INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
    INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
    WHERE
    wp_terms.slug = 'home-slideshow-mouseovers' AND wp_postmeta.meta_key = 'slot'
    ORDER BY
    wp_postmeta.meta_value

    Thank you for your help & for steering me in the right direction. I appreciate it hugely.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    ...
    INNER 
      JOIN wp_postmeta 
        ON wp_postmeta.post_id = wp_posts.ID
       AND wp_postmeta.meta_key = 'slot'
     WHERE wp_terms.slug = 'home-slideshow-mouseovers' 
    ORDER 
        BY wp_postmeta.meta_value
    also, please remove the parentheses you added to the ON conditions

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

  10. #10
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937. Now what if I wanted to grab 2 values from the wp_postmeta table, both with the same post_id, but different meta_key.

    Such as

    SELECT
    wp_posts.ID,
    wp_posts.post_content,
    wp_postmeta.meta_value as meta_value1
    wp_postmeta.meta_value as meta_value2
    FROM
    wp_terms
    INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
    INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
    WHERE
    wp_terms.slug = 'home-slideshow-mouseovers' AND meta_value1 = 'slot' and meta_value2 = 'Read More Link'
    ORDER BY
    meta_value1

    It'd seem to me that iterating through that result using mysql_result("query", $x, "meta_value1") to spit out the 'slot' value and mysql_result("query", $x, "meta_value2") to spit out the 'Read More Link' value would be logical. Does it seem like there would be any problem or smarter way to do this, to you?

  11. #11
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Querying Same Table, Same Field, but using "AS" to return data

    I have been working on using a JOIN to create a pool of data across 3 different tables, and to sort it according to "slot" values in another table. The query works great. Now I'm looking to return data in the rows "wp_postmeta.meta_value" as some sort of alias that I can call later when I iterate through the result set.

    SELECT
    wp_posts.ID,
    wp_posts.post_content,
    wp_postmeta.meta_value
    FROM
    wp_terms
    INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
    INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
    WHERE
    wp_terms.slug = 'home-slideshow-mouseovers' AND wp_postmeta.meta_value
    = 'slot'
    ORDER BY
    wp_postmeta.meta_value

    ^ The above works fine.

    It's just that the wp_postmeta table is going to contain data like this:

    Table wp_postmeta
    post_id meta_value meta_key
    400 slot 1
    401 slot 3
    402 slot 2
    400 Read More /news/400_1/
    401 Read More /news/401_1/
    402 Read More /news/402_1/

    And I would like to query the wp_postmeta table for the meta_key under the meta_value of "Read More" while I query it for "slot".

    I've tried designating the different value as some sort of alias, like below:

    SELECT
    wp_posts.ID,
    wp_posts.post_content,
    wp_postmeta.meta_value as meta_value1
    wp_postmeta.meta_value as meta_value2
    FROM
    wp_terms
    INNER JOIN wp_term_relationships ON (wp_term_relationships.term_taxonomy_id = wp_terms.term_id)
    INNER JOIN wp_posts ON (wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN wp_postmeta ON (wp_postmeta.post_id = wp_posts.ID)
    WHERE
    wp_terms.slug = 'home-slideshow-mouseovers' AND meta_value1 = 'slot' and meta_value2 = 'Read More Link'
    ORDER BY
    meta_value1

    It's just that something's off as far as my attempts at aliasing (in bold).

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT wp_posts.ID
         , wp_posts.post_content
         , meta1.meta_value as meta_value1
         , meta2.meta_value as meta_value2
      FROM wp_terms
    INNER 
      JOIN wp_term_relationships 
        ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id
    INNER 
      JOIN wp_posts 
        ON wp_posts.ID = wp_term_relationships.object_id
    LEFT OUTER 
      JOIN wp_postmeta AS meta1
        ON meta1.post_id = wp_posts.ID
       AND meta1.meta_key = 'slot'
    LEFT OUTER 
      JOIN wp_postmeta AS meta2
        ON meta2.post_id = wp_posts.ID
       AND meta2.meta_key = 'Read More Link'
     WHERE wp_terms.slug = 'home-slideshow-mouseovers'
    i made them LEFT OUTER JOINs as i wasn't sure if you wanted them mandatory... change to INNER joins if you do

    i also took off your ORDER BY clause because it no longer made sense, feel free to add it back with the appropriate column(s)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This example is huge to help me get a better grasp on this stuff. It works perfectly, I added back in the ORDER BY. Thank you.


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
  •