February 21, 2013 at 11:57 am
I want to join two tables order_ship and ship_id. I want to join on location and order_no which both tables have the problem is my next field are order_ship.ship_num which is always one of two values 000001 or 100001 and then ship_id.ship_type which is always TypeA or TypeB.
order_ship.ship_num value of 000001 equals ship_id.ship_type value of TypeA
and
order_ship.ship_num value of 100001 equals ship_id.ship_type value of ship_id.TypeB
The table are the same other than these to fields.
February 21, 2013 at 5:35 pm
Resolver table?
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;
_____________________________________________________________________
- Nate
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply