Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Two tables and want to join on different columns Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 11:57 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 11:51 AM
Points: 97, Visits: 363
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.





Post #1422737
Posted Thursday, February 21, 2013 5:35 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Wednesday, September 24, 2014 7:34 AM
Points: 620, Visits: 866
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
Post #1422831
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse