SitePoint Sponsor

User Tag List

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

    Big Fat JOIN plus a Big Fat JOIN

    Well, this is some puzzle. What I'm trying to do is basically just use an "AND" at the end of this query, but my gut is telling me that I've got to redesign the JOINS for this to work.

    What I have is some content and taxonomy spread out across 3 tables.

    It goes like this (only relevant cols included):

    wp_posts
    [id (wp_term_relationships.object_id)] [post_content]

    wp_term_relationships
    [object_id (wp_posts.id)] [term_taxonomy_id (wp_terms.term_id)]

    wp_terms
    [term_id (wp_term_relationships.term_taxonomy_id)] [slug]

    The relationships are in parentheses.

    I have a few rows of content in wp_posts that are part of two categories, one being a parent and one being a child. So listed under wp_term_relationships, I have:

    wp_term_relationships
    [object_id] [term_taxonomy_id]
    130............8
    130............12
    128............8
    128............11

    When I run my original query, it's based off of the slug name of one category, which traced through the join retrieves term_taxonomy_id "8" and returns both id 130 and 128 in wp_posts.

    What I'd like to do is check for 2 slug names (the parent and the child), and only return ID's that have both slug names, so basically, find the id that is part of both category "8" and category "12", and only return the 130.

    My current query is this:

    SELECT wp_posts.post_content 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 WHERE wp_terms.slug = 'homeblocks'

    What I want to end up with, basically is just adding " AND wp_terms.slug='1x2'" at the end, like this:

    SELECT wp_posts.post_content 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 WHERE wp_terms.slug = 'homeblocks' AND wp_terms.slug='1x2'

    but I know that won't work, and I've tried it.

    I'm thinking I've got to wrap some joins inside each other, like:

    SELECT wp_posts.post_content 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 WHERE wp_terms.slug = 'homeblocks') INNER JOIN (wp_term_relationships ON wp_term_relationships.term_taxonomy_id = wp_terms.term_id AND INNER JOIN wp_posts ON wp_posts.ID=wp_term_relationships.object_id WHERE wp_terms.slug = '1x2')

    I'd appreciate any guidance, big-time.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,246
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by jlipinski3 View Post
    ... find the id that is part of both category "8" and category "12", and only return the 130.
    Code:
    SELECT object_id
      FROM wp_term_relationships
     WHERE term_taxonomy_id IN ( 8,12 )
    GROUP
        BY object_id
    HAVING COUNT(*) = 2
    that returns the objects, you can then join again if you need object name, etc.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    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 object_id
      FROM wp_term_relationships
     WHERE term_taxonomy_id IN ( 8,12 )
    GROUP
        BY object_id
    HAVING COUNT(*) = 2
    that returns the objects, you can then join again if you need object name, etc.
    Thanks r937. It makes sense, but the biggest thing is that I'm trying to join so that I don't manually input the "8, 12". Those are joined with the terms table as "slug"

    wp_terms
    [term_id] [slug]
    8............homeblocks
    10..........1x2
    12..........2x2

    Trying to make some logic out of it, it'd go like this:

    Code:
    SELECT object_id
      FROM wp_term_relationships
     WHERE term_taxonomy_id IN (
     INNER JOIN wp_terms
    ON wp_terms.term_id=wp_term_relationships.term_taxonomy_id
    WHERE wp_terms.slug=("homeblocks", "1x2") 
    )
    GROUP
        BY object_id
    HAVING COUNT(*) = 2
    And I'm not surprised, but even with my awesome homemade syntax, the query doesn't work.

  4. #4
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    449
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    something like this should work for ya:
    Code:
    SELECT tr.object_id
    FROM wp_term_relationships AS tr
    INNER JOIN wp_terms AS t 
    	ON t.term_id = tr.term_taxonomy_id
    	AND t.slug IN ('homeblocks', '1x2') 
    GROUP BY tr.object_id
    HAVING COUNT(*) = 2

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,263
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by jlipinski3 View Post
    And I'm not surprised, but even with my awesome homemade syntax, the query doesn't work.
    That's because the join syntax isn't correct. Try this

    Code MySQL:
    SELECT object_id
      FROM wp_term_relationships r
     INNER JOIN wp_terms t ON t.term_id = r.term_taxonomy_id
     WHERE t.slug IN ("homeblocks", "1x2") 
     GROUP BY object_id
     HAVING COUNT(*) = 2
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    SitePoint Enthusiast
    Join Date
    Nov 2009
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by transio View Post
    something like this should work for ya:
    Code:
    SELECT tr.object_id
    FROM wp_term_relationships AS tr
    INNER JOIN wp_terms AS t 
    	ON t.term_id = tr.term_taxonomy_id
    	AND t.slug IN ('homeblocks', '1x2') 
    GROUP BY tr.object_id
    HAVING COUNT(*) = 2
    Transio, I never thanked you. But this code works flawlessly, and it's still very useful to me so I use it frequently.


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
  •