Relationship between two taxonomies and a custom post type

for a project, I’ve a CPT products with two taxonomies : category and brand.

I would like to build a navigation from a category to a product under a brand.

• page 1 : list the categories terms
• page 2 : list the brand term having the selected category term
• page 3 : list the products under the brand and the category
• page 4 : display the product

Obviously, page 1 and 4 are easy to do but i can’t figure out how to retrieve the terms in the page 2 and the products in the page 3.

I’m sure there is a way to do that. Any idea ?

Thanks.

Cyril

UPDATE 1

I have found a way to display the correct brands under the page 2 :

<?php
$term = get_term_by('slug',get_query_var('term'),'product_category');

$q1 = "
    SELECT DISTINCT p1.ID
    FROM
        $wpdb->posts p1
        INNER JOIN $wpdb->term_relationships tr1 ON (p1.ID = tr1.object_id)
        INNER JOIN $wpdb->term_taxonomy tt1 ON (tr1.term_taxonomy_id = tt1.term_taxonomy_id)
    WHERE tt1.term_id = '$term->term_id'
    ";

$q2 = "
    SELECT tt2.term_id
    FROM
        $wpdb->posts p2
        INNER JOIN $wpdb->term_relationships tr2 ON (p2.ID = tr2.object_id)
        INNER JOIN $wpdb->term_taxonomy tt2 ON (tr2.term_taxonomy_id = tt2.term_taxonomy_id)
    WHERE p2.ID IN ($q1)
    ";

$q3 = "
    SELECT tt3.*
    FROM
        $wpdb->posts p3
        INNER JOIN $wpdb->term_relationships tr3 ON (p3.ID = tr3.object_id)
        INNER JOIN $wpdb->term_taxonomy tt3 ON (tr3.term_taxonomy_id = tt3.term_taxonomy_id)
        INNER JOIN $wpdb->terms t3 ON t3.term_id = tt3.term_id
    WHERE 1=1
        AND tt3.term_id IN ($q2)
        AND tt3.taxonomy = 'product_brand'
    ORDER BY t3.name ASC
    ";

$brands = $wpdb->get_results($q3);
?>

I now need to know the category chosen in the taxonomy-brand.php file, any suggestion ?

UPDATE 2

Thanks to this thread : Custom post type taxonomies URL rewrite, I now have the correct navigation from the homepage to a product page. Just need to filter the results in the brand page.