T-SQL combine indirectly related tables into single query

Question

I have a parent table called "Businesses" with two different related/child tables (Affiliates and Employees) related to a business but not directly to each other.

I want a single SQL query that will show all businesses, affiliates and employees with one line item for each employee. For example, given business "B1" with 2 Affiliates (A1 and A2) and two Employees (E1 and E2), I want a query with results that look something like:

Business	Affiliates	Empl #	Empl Name
B1	A1, A2	1	John
B1	A1, A2	2	Bob

The closest I've gotten is output that looks like:

Business	Affiliates	Empl #	Empl Name
B1	A1	1	John
B1	A1	2	Bob
B1	A2	1	John
B1	A2	2	Bob

But as you can see, each employee shows twice (once for each affiliate).

Any suggestions on how to achieve the desired result?

Answer 1

You could do this.

DECLARE @Sample TABLE ( business char(12), affiliates VARCHAR(25), empid int);

INSERT INTO @Sample VALUES
    ( 'B1', 'A1', 50 ),
    ( 'B1', 'A1', 80 ),
    ( 'B1', 'A2', 90 ),
    ( 'B1', 'A2', 50 );
    
SELECT  O.business,
        STUFF( (SELECT DISTINCT ', ' + CAST(affiliates AS VARCHAR(25)) 
                FROM @Sample I WHERE I.business = O.business FOR XML PATH('')
                ), 1, 2, '')
FROM    @Sample O
GROUP BY O.business;

Result:

Change to suit your needs.