Big Fat JOIN plus a Big Fat JOIN

Databases
1

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 
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.

3

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:

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

something like this should work for ya:


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

That’s because the join syntax isn’t correct. Try this


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
6

Transio, I never thanked you. But this code works flawlessly, and it’s still very useful to me so I use it frequently.