Rod0n Rod0n - 5 months ago 12
MySQL Question

Filter rows without a specific value

I'm querying a wordpress mysql db outside the php language and I don't know how to solve the following problem: each post has taxonomies with some value. There's a value called 'calificaciones' which I'd like to filter on. This is my current query:

SELECT wp_posts.post_title, IF (wp_term_taxonomy.taxonomy = 'calificaciones', wp_terms.slug, 'no') as calificacion
FROM wp_posts
JOIN wp_term_relationships ON (wp_term_relationships.object_id = wp_posts.ID)
JOIN wp_term_taxonomy ON (wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id)
JOIN wp_terms ON (wp_terms.term_id = wp_term_taxonomy.term_id)

AND wp_posts.post_type = 'ultimas-noticias'
#AND wp_term_taxonomy.taxonomy = 'calificaciones'


The problem with this query is that I'll get the post_title multiple times(because there are others taxonomies for each post). I'd like to get the post title and a flag yes-no, if the post has that taxonomy value or not.

Answer

Instead of joining the taxonomies on the same query you could use a sub query for the calificacion column.

SELECT wp_posts.post_title, CASE WHEN EXISTS((SELECT *
    FROM wp_term_relationships
    JOIN wp_term_taxonomy ON (wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id)
    JOIN wp_terms ON (wp_terms.term_id = wp_term_taxonomy.term_id)
    WHERE (wp_term_relationships.object_id = wp_posts.ID)
    AND wp_term_taxonomy.taxonomy = 'calificaciones'
    )) THEN 'YES' ELSE 'NO' END as calificacion
FROM wp_posts
WHERE wp_posts.post_type = 'ultimas-noticias'