InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 06:36:02 GMT20http://www.sqlservercentral.com/Forums/Topic1422737-338-1.aspxResolver table?[code="sql"]use tempdbgodeclare @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;[/code]Thu, 21 Feb 2013 17:35:48 GMTRP_DBAhttp://www.sqlservercentral.com/Forums/Topic1422737-338-1.aspxI 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 TypeAandorder_ship.ship_num value of 100001 equals ship_id.ship_type value of ship_id.TypeBThe table are the same other than these to fields.Thu, 21 Feb 2013 11:57:37 GMTtwdavis-893252