CODEWITHSUNDEEP
sqldb2
Yaza
Yaza

Reputation: 553

Selecting records from table A based in multiple records in B

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?

Upvotes: 0

Views: 128

Answers (3)

dcolazin
dcolazin

Reputation: 1074

If for any reason you need to return/use the manager, you can use something like the following:

SELECT ORD.*, IFNULL(MSR.Manager,ORD.Sales) as ActualManager 
FROM ORD
LEFT JOIN MSR
 ON (ORD.Sales = MSR.Sales and ORD.Region = MSR.Region)
WHERE IFNULL(MSR.Manager,ORD.Sales) = 'MA'
ORDER BY OrderId

Upvotes: 2

Angesehen
Angesehen

Reputation: 311

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

Upvotes: 2

Aman
Aman

Reputation: 99

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

Upvotes: 0

Related Questions