use tempdb
go

declare @order_ship table (
	location char(5)
	, order_no int
	, ship_num char(6)
);

declare @ship_id table (
	location char(5)
	, order_no int
	, ship_type char(5)
);

declare @resolver table (
	ship_num char(6)
	, ship_type char(5)
);

insert into @order_ship values ('US', 1, '000001'), ('US', 2, '000001'), ('US', 3, '100001'), ('US', 4, '100001');
insert into @ship_id values ('US', 1, 'TypeA'), ('US', 2, 'TypeA'), ('US', 3, 'TypeB'), ('US', 4, 'TypeB');

insert into @resolver values ('000001', 'TypeA'), ('100001', 'TypeB');

select	*
from	@order_ship o
		inner join @resolver r on o.ship_num = r.ship_num
		inner join @ship_id s
			on o.location = s.location
			and o.order_no = s.order_no
			and s.ship_type = r.ship_type;