CODEWITHSUNDEEP
mysql
lancemonotone
lancemonotone

Reputation: 135

MySQL: Need JOIN expertise

Notice the nested subqueries. I know there's a better way to make this happen with a JOIN. I just don't have the expertise to figure it out.

SELECT DISTINCT pm.post_id 
FROM wp_3_postmeta pm
WHERE pm.meta_key LIKE 'member_categories_%_member_categories_name'
AND pm.meta_value IN(
    SELECT tt.term_taxonomy_id
    FROM wp_3_term_taxonomy tt
    WHERE tt.parent IN(
        SELECT tt.term_taxonomy_id
        FROM wp_3_term_taxonomy tt
        WHERE tt.parent = 33)
    OR tt.parent = 33
);

Thanks for your help!

Upvotes: 0

Views: 58

Answers (2)

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

This should do the trick for you

SELECT DISTINCT pm.post_id 
FROM wp_3_postmeta pm
     INNER JOIN wp_3_term_taxonomy tt ON pm.meta_value = tt.term_taxonomy_id
     LEFT JOIN wp_3_term_taxonomy tt2 ON tt.parent = tt2.term_taxonomy_id
WHERE pm.meta_key LIKE 'member_categories_%_member_categories_name'
   AND (tt.parent = 33 OR tt2.parent = 33)

Upvotes: 1

bhamby
bhamby

Reputation: 15450

I think this should get what you're wanting:

SELECT DISTINCT pm.post_id
FROM wp_3_postmeta pm
JOIN wp_3_term_taxonomy tt
  ON tt.term_taxonomy_id = pm.meta_value
LEFT JOIN wp_3_term_taxonomy t2
  ON t2.term_taxonomy_id = tt.parent
WHERE pm.meta_key LIKE 'member_categories_%_member_categories_name'
  AND (t2.parent = 33 OR tt.parent = 33)

Upvotes: 0

Related Questions