Two tables and want to join on different columns

• twdavis-893252

  Ten Centuries

  Points: 1195

  More actions

  February 21, 2013 at 11:57 am

  #270771

  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.

• RP_DBA

  SSCertifiable

  Points: 5116

  More actions

  February 21, 2013 at 5:35 pm

  #1590168

  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

  @nate_hughes