Advanced MySQL: Combining queries

  • Resolved monkeyangst

    (@monkeyangst)


    16 years, 3 months ago

    Hi everyone,
    I’m beating my head against a wall trying to figure out how I can use MySQL to combine a couple of queries… the problem is they’re queries using the same tables.

    I know how to write an SQL query that finds posts in a certain category. This clause:

    INNER JOIN wp_term_relationships as a
ON (wp_posts.ID = a.object_id)
INNER JOIN wp_term_taxonomy
ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE 1=1
AND wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN ('3', '3')

    will limit the query to category 3, for instance. Likewise, I know how to search for particular tags — it’s the same clause, with ‘post_tag’ instead of ‘category’ etc.

    However, what I’m trying to do is find posts that are BOTH in a certain category and have a certain tag. In category 3 with tag 8, for instance. I just can’t figure out how to structure the MySQL query. Anyone know how to do this?

Viewing 4 replies - 1 through 4 (of 4 total)
  • Mark / t31os

    (@t31os_)

    16 years, 3 months ago

    See the discussion here for some examples.
    http://wordpress.org/support/topic/276635

    vtxyzzy

    (@vtxyzzy)

    16 years, 3 months ago

    I think what you want is ‘AND wp_post.ID in (SELECT …’. The query below should do it (don’t know why you used “IN (‘3′,’3’)” instead of just ” = ‘3’”, though):

    select * from wp_posts
INNER JOIN wp_term_relationships as a
ON (wp_posts.ID = a.object_id)
INNER JOIN wp_term_taxonomy
ON (a.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
WHERE wp_term_taxonomy.taxonomy = 'category'
AND wp_term_taxonomy.term_id IN ('3', '3')
AND wp_posts.ID IN
(SELECT b.object_id
FROM wp_term_relationships as b,
     wp_term_taxonomy as c
WHERE b.term_taxonomy_id = c.term_taxonomy_id
AND c.taxonomy = 'post_tag'
AND c.term_id IN ('8','8'))
    Thread Starter monkeyangst

    (@monkeyangst)

    16 years, 3 months ago

    Thanks for the responses! I figured out one way of achieving what I want, using aliases to the tables. I’ll check out what others are doing, maybe it’s better.

    vtxyzzy

    (@vtxyzzy)

    16 years, 3 months ago

    OK – glad you got it. Please mark the topic Resolved.

Viewing 4 replies - 1 through 4 (of 4 total)

The topic ‘Advanced MySQL: Combining queries’ is closed to new replies.