Conditional join

Given 2 tables, Transactions and Instructions, my goal is to join a
transaction to its appropriate instruction. The 3 fields I can possibly join
on are: Exchange, SecType, and Country. All of these fields are nullable
in the instruction table. Not my design, I can't change this.
How can I join these tables so that any null values are disregarded. Given
my sample data I want to join the following records.
TransactionID,InstructionID
1,1
2,4
3,7
4,10
CREATE TABLE [dbo].[#Transactions] (
[TransactionID] [int] NOT NULL ,
[Exchange] [char] (3) NOT NULL ,
[SecType] [char] (4) NOT NULL ,
[Country] [char] (3) NOT NULL
) ON [PRIMARY]
GO
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(1,'NYS','COM','USA')
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(2,'LSE','COM','GBR')
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(3,'TSE','ADR','CAN')
INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
(4,'NAS','COM','USA')
CREATE TABLE [dbo].[#Instructions] (
[TransactionID] [int] NOT NULL ,
[Exchange] [char] (3)NOT NULL ,
[SecType] [char] (4),
[Country] [char] (3),
[Instruction][char] (25)
) ON [PRIMARY]
GO
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(1,'NYS',NULL,NULL,'Instruction#1')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(2,'NYS','ADR',NULL,'Instruction#2')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(3,'LSE','ADR','GBR','Instruction#3')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(4,'LSE','COM',NULL,'Instruction#4')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(5,'LSE','COM','IRL','Instruction#5')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(6,'TSE','COM','CAN','Instruction#6')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(7,'TSE','ADR','CAN','Instruction#7')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(8,'TSE',NULL,NULL,'Instruction#8')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(9,'NAS',NULL,NULL,'Instruction#9')
INSERT INTO #Instructions
(TransactionID,Exchange,SecType,Country,
Instruction) VALUES
(10,'NAS',NULL,'USA','Instruction#10')Terri wrote:
> Given 2 tables, Transactions and Instructions, my goal is to join a
> transaction to its appropriate instruction. The 3 fields I can possibly jo
in
> on are: Exchange, SecType, and Country. All of these fields are nullable
> in the instruction table. Not my design, I can't change this.
> How can I join these tables so that any null values are disregarded. Given
> my sample data I want to join the following records.
> TransactionID,InstructionID
> 1,1
> 2,4
> 3,7
> 4,10
> CREATE TABLE [dbo].[#Transactions] (
> [TransactionID] [int] NOT NULL ,
> [Exchange] [char] (3) NOT NULL ,
> [SecType] [char] (4) NOT NULL ,
> [Country] [char] (3) NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (1,'NYS','COM','USA')
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (2,'LSE','COM','GBR')
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (3,'TSE','ADR','CAN')
> INSERT INTO #Transactions (TransactionID,Exchange,SecType,Country)
VALUES
> (4,'NAS','COM','USA')
> CREATE TABLE [dbo].[#Instructions] (
> [TransactionID] [int] NOT NULL ,
> [Exchange] [char] (3)NOT NULL ,
> [SecType] [char] (4),
> [Country] [char] (3),
> [Instruction][char] (25)
> ) ON [PRIMARY]
> GO
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (1,'NYS',NULL,NULL,'Instruction#1')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (2,'NYS','ADR',NULL,'Instruction#2')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (3,'LSE','ADR','GBR','Instruction#3')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (4,'LSE','COM',NULL,'Instruction#4')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (5,'LSE','COM','IRL','Instruction#5')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (6,'TSE','COM','CAN','Instruction#6')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (7,'TSE','ADR','CAN','Instruction#7')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (8,'TSE',NULL,NULL,'Instruction#8')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (9,'NAS',NULL,NULL,'Instruction#9')
> INSERT INTO #Instructions
> (TransactionID,Exchange,SecType,Country,
Instruction) VALUES
> (10,'NAS',NULL,'USA','Instruction#10')
As I expect you realise, both tables look like they badly need a
redesign. Meantime, try this:
SELECT T.transactionid,
COALESCE(I1.instructionid, I2.instructionid,
I3.instructionid, I4.instructionid)
FROM #transactions AS T
LEFT JOIN #instructions AS I1
ON T.exchange = I1.exchange
AND T.sectype = I1.sectype
AND T.country = I1.country
LEFT JOIN #instructions AS I2
ON I2.sectype IS NULL
AND T.exchange = I2.exchange
AND T.country = I2.country
LEFT JOIN #instructions AS I3
ON I3.country IS NULL
AND T.exchange = I3.exchange
AND T.sectype = I3.sectype
LEFT JOIN #instructions AS I4
ON I4.sectype IS NULL
AND I4.country IS NULL
AND T.exchange = I4.exchange ;
David Portas
SQL Server MVP
--