Get categories that contain entries that are in the current category

Question

How can I get a list of categories that share entries with the currently selected category.

Huh? Let me explain

• I have a channel called Portfolio
• Portfolio channel has two category groups (client and role)
• I have a page listing all clients (categories)
• I would like to list all clients who I have made a website (for example) for

Answer 1

{preload_replace:group_to_display="1"}
{preload_replace:current_category_id="15"}

{exp:query sql="
    SELECT DISTINCT `cats`.*
    FROM `exp_category_posts` AS `posts`
    INNER JOIN `exp_categories` AS `cats`
    ON `posts`.`cat_id` = `cats`.`cat_id`
    WHERE `group_id` = {group_to_display}
    AND `entry_id` IN (
        SELECT DISTINCT `entry_id`
        FROM `exp_category_posts`
        WHERE `cat_id` = {current_category_id}
    )
    ORDER BY `cat_name` ASC;
"}
    {cat_name}
    {cat_url_title}
    etc...
{/exp:query}

I have used preload replace variables to show where the variables would go in the query. In reality you would use Low Seg2cat or something similar.