CODEWITHSUNDEEP
sqlwordpress
richelliot
richelliot

Reputation: 586

Simple SQL query issue in wordpress

I have a really strange problem.

I simply want to select all posts that feature in two given categories.

Heres my SQL:

SELECT  wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
WHERE  wp_term_relationships.term_taxonomy_id  = 83  
   AND   wp_term_relationships.term_taxonomy_id  = 84

This should return one result but it doesnt return anything.

If I comment out:

*wp_term_relationships.term_taxonomy_id  = 83*

The post im looking for is returned.

If I comment out:

*wp_term_relationships.term_taxonomy_id  = 84*

Again, the post is returned.

Why then,when both of these conditions are included, nothing is returned?

Any help will be appreciated.Thanks

Upvotes: 0

Views: 1170

Answers (5)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

The wp_term_relationships.term_taxonomy_id couldn't be =83 and =84 in the same time.

Try this instead:

SELECT  
  wp_posts.ID 
FROM wp_posts 
INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
WHERE  wp_term_relationships.term_taxonomy_id IN (83 , 48);

OR 

...
WHERE wp_term_relationships.term_taxonomy_id  = 83  
   OR wp_term_relationships.term_taxonomy_id  = 84;

But, I think you are looking for those posts that have both term_taxonomy_id' ids 83, 84 in the same time, and in this case , you are looking for the relation division; here is one way to do so:

SELECT  
  wp_posts.ID 
FROM wp_posts 
INNER JOIN
(
   SELECT *
   FROM wp_term_relationships
   WHERE object_id IN (SELECT object_id
                       FROM  wp_term_relationships
                       WHERE term_taxonomy_id IN (83 , 48);
                       GROUP BY objecT_id
                       HAVING COUNT(term_taxonomy_id ) = 2)
)  t ON wp_posts.ID = t.object_id;

Or: Directly, without JOIN. Like so:

SELECT  
  wp_posts.ID 
FROM wp_posts 
WHERE ID IN
(
    SELECT object_id
    FROM  wp_term_relationships
    WHERE term_taxonomy_id IN (83 , 48);
    GROUP BY objecT_id
    HAVING COUNT(term_taxonomy_id ) = 2
);

Upvotes: 2

Taryn
Taryn

Reputation: 247650

If you want to return all posts that have both those term_taxonomy_id's associated with them you can use:

SELECT  p.ID 
FROM wp_posts p
INNER JOIN wp_term_relationships r
  ON p.ID = r.object_id
WHERE  r.term_taxonomy_id IN (83, 84)
GROUP BY p.ID 
HAVING count(distinct r.term_taxonomy_id) =2

This will return all records that have both taxonomy terms.

Your query will not work because the term_taxonomy_id cannot have two values at the same time. You can change your query to use OR instead of AND or you can use the version I provided.

Upvotes: 1

Dave Richardson
Dave Richardson

Reputation: 4995

Your query says :

WHERE  wp_term_relationships.term_taxonomy_id  = 83  
AND    wp_term_relationships.term_taxonomy_id  = 84

This will return nothing, I suspect you should change AND to OR

Upvotes: 0

Petter Brodin
Petter Brodin

Reputation: 2189

I assume you need to use "OR" rather than "AND". My guess is that you're not looking for the post where the taxonomy_id is BOTH 83 and 84, but where it's one of the two?

Upvotes: 0

twoleggedhorse
twoleggedhorse

Reputation: 5048

If you want the results to show posts that are in both categories, you need to get both results and compare them:

SELECT a.ID
FROM   (
        SELECT  wp_posts.ID 
        FROM wp_posts 
        INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
        WHERE  wp_term_relationships.term_taxonomy_id  = 83
        ) a
        INNER JOIN (
                    SELECT  wp_posts.ID 
                    FROM wp_posts 
                    INNER JOIN wp_term_relationships ON wp_posts.ID = wp_term_relationships.object_id
                    WHERE wp_term_relationships.term_taxonomy_id  = 84
                    ) b
            ON a.ID = b.ID

Upvotes: 0

Related Questions