Wordpress: Displaying all the post tags from within a specific taxonomy's term

Question

Okay so I understand that the default "Categories" is a taxonomy, and posts live within those categories.

I created a new Taxonomy (like Categories) that houses a custom post type. The posts of that post type also have tags.

[New Category-Type Taxonomy]
   1. Category Name 1
      a. Custom Post 1
           This entry has post tags
      b. Custom Post 2
           This entry has tags as well
      c. Custom Post 3
           And this one too

   2. Category Name 2
      a. Custom Post 4
           This entry has post tags
      b. Custom Post 5
           This entry has tags as well
      c. Custom Post 6
           And this one too

What I'm trying to do is round all the posts tags from the Category Names

So I can list 1a 1b 1c on the Category 1 page, and

2a 2b 2c on the Category 2 page.

If I can figure out what to query or how to navigate through arrays I can do some kind of for each loop and get these post tags into

tags that can I add jQuery to for extended functionality.

Answer 1

http://wordpress.org/support/topic/get-tags-specific-to-category

$category_tags = $wpdb->get_results("
SELECT DISTINCT
terms2.term_id as tag_ID,
terms2.name as tag_name,
t2.count as posts_with_tag
FROM
$wpdb->posts as p1
LEFT JOIN $wpdb->term_relationships as r1 ON p1.ID = r1.object_ID
LEFT JOIN $wpdb->term_taxonomy as t1 ON r1.term_taxonomy_id = t1.term_taxonomy_id
LEFT JOIN $wpdb->terms as terms1 ON t1.term_id = terms1.term_id,

$wpdb->posts as p2
LEFT JOIN $wpdb->term_relationships as r2 ON p2.ID = r2.object_ID
LEFT JOIN $wpdb->term_taxonomy as t2 ON r2.term_taxonomy_id = t2.term_taxonomy_id
LEFT JOIN $wpdb->terms as terms2 ON t2.term_id = terms2.term_id
WHERE (
t1.taxonomy = 'category' AND
p1.post_status = 'publish' AND
terms1.term_id = '$varcat' AND
t2.taxonomy = 'post_tag' AND
p2.post_status = 'publish' AND
p1.ID = p2.ID
)
");

change 'category' to your custom taxonomy name