Selecting records from table A based in multiple records in B

Question

I have the following sales structure. Managers are assigned a salesperson per region but also sell themselves. The table below holds the link between managers and salespersons/regions.

TABLE: MSR

Manager	Sales	Region
MA	SA	EAST
MA	SB	EAST
MA	SB	NORTH
MB	SA	WEST
MB	SB	WEST

This table lists the orders that each person scored.

TABLE: ORD

OrderId	Sales	Region
1	MA	EAST
2	MA	WEST
3	SA	WEST
4	SA	EAST
5	SB	EAST
6	SB	WEST
7	MB	NORTH
8	SB	NORTH

I want an order output per manager. For example for manager MA it would look like this:

OrderId	Sales	Region
1	MA	EAST
2	MA	WEST
4	SA	EAST
5	SB	EAST
8	SB	NORTH

For MB it looks like this:

OrderId	Sales	Region
3	SA	WEST
6	SB	WEST
7	MB	NORTH

Is there an SQL statement that can produce this result?

Answer 1

The below query would get you the results you want, you would just have to swap out the where to be whatever manager that you were looking for, this returns what you're looking for, for MA.

SELECT Distinct A.OrderId, A.Sales, A.Region FROM ORD a 
left join MSR b on (a.Sales = b.Sales OR B.Manager = A.Sales) and a.Region = B.Region 
where B.Manager = 'MA' or A.Sales = 'MA'
order by orderid asc

Answer 2

With cte_MA as (
Select Distinct sales from mar where manager = 'MA' 
Union 
Select Distinct manager from mar where manager = 'MA' ),
Cte_MB as (
Select Distinct sales from mar where manager = 'MB' 
Union 
Select Distinct manager from mar where manager = 'MB'),
Cte_MAorder as (
Select a orderid, a.sales, a region from ord a inner cte_MA b on a.sales = b.sales),
Cte_MBorder as (
Select a.orderid, a.sales, a.region from ord a inner cte_MB b on a.sales = b.sales)
select *  from cte_MAorder;
Select *  from cte_MBorder;

You should run all query with to up select query if you run select * from cte_maorder query you get MA assigned salesperson order list you run select * from cte_mborder query you get MB assigned salesperson order list