Conditional Join

I want to join 2 tables conditionally. One order needs to join with one
instruction. The three potential join fields are: Country, Exchange, and
Type. These fields are required in the Orders table but only Country is
required in Instructions. The data entry requirements of the application are
such that if an instruction has a Type it must have an Exchange.
The logic of the join is that:
1-if all three fields match, Country, Exchange, and Type then join those
records.
2-if two fields match, Country, and Exchange then join those records.
3-if one field matches, Country, then join those records.
My expected results given the sample data is as follows.
SELECT OrderID,InstructionID FROM Orders
JOIN ...
--Expected Results
OrderID,InstructionID
1,1
2,5
3,5
4,7
5,8
6,9
7,13
CREATE TABLE Orders
(
OrderID int NOT NULL,
Country char (3)NOT NULL,
Exchange char (3)NOT NULL,
Type char (3)NOT NULL,
)
CREATE TABLE Instructions
(
InstructionID int NOT NULL,
Country char (3) NOT NULL,
Exchange char (3) NULL,
Type char (3) NULL,
Instructions varchar (15)NOT NULL
)
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (1,'USA','NYS','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (2,'CAN','TSE','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (3,'CAN','TSE','Sel')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (4,'ESP','BAR','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (5,'ESP','MAD','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (6,'IRQ','BAG','Buy')
INSERT Orders (OrderID,Country,Exchange,Type)VALUES (7,'DUE','HAM','Buy')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(1,'USA','NYS','Buy','Instruction 1')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(2,'USA','NYS','Sel','Instruction 2')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(3,'USA','NYS',NULL,'Instruction 3')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(4,'USA',NULL,NULL,'Instruction 4')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(5,'CAN','TSE',NULL,'Instruction 5')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(6,'CAN','ALB',NULL,'Instruction 6')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(7,'ESP',NULL,NULL,'Instruction 7')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(8,'ESP','MAD',NULL,'Instruction 8')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(9,'IRQ','BAG','Buy','Instruction 9')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(10,'IRQ','BAG','Sel','Instruction 10 ')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(11,'DUE',NULL,NULL,'Instruction 11')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(12,'DUE','HAM',NULL,'Instruction 12')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(13,'DUE','HAM','Buy','Instruction 13')
INSERT Instructions (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
(14,'DUE','HAM','Sel','Instruction 14')Terri (terri@.cybernets.com) writes:
> I want to join 2 tables conditionally. One order needs to join with one
> instruction. The three potential join fields are: Country, Exchange, and
> Type. These fields are required in the Orders table but only Country is
> required in Instructions. The data entry requirements of the application
> are such that if an instruction has a Type it must have an Exchange.
> The logic of the join is that:
> 1-if all three fields match, Country, Exchange, and Type then join those
> records.
> 2-if two fields match, Country, and Exchange then join those records.
> 3-if one field matches, Country, then join those records.
> My expected results given the sample data is as follows.
Thanks a lot for table and test data. This may not be the smartest
query, but it's easy to understand:
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
WHERE NOT EXISTS (SELECT *
FROM Instructions I2
WHERE O.Country = I2.Country
AND O.Exchange = I2.Exchange )
UNION ALL
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
AND O.Exchange = I.Exchange
WHERE NOT EXISTS (SELECT *
FROM Instructions I2
WHERE O.Country = I2.Country
AND O.Exchange = I2.Exchange
AND O.Type = I2.Type)
UNION ALL
SELECT O.OrderID, I.InstructionID
FROM Orders O
JOIN Instructions I ON O.Country = I.Country
AND O.Exchange = I.Exchange
AND O.Type = I.Type
ORDER BY OrderID
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> I want to join 2 tables conditionally. <<
That makes NO sense as you have to join or not join a table.
Okay.
Gee, too bad that SQL and RDBMS has columns and not fields; rows are
not records. They are nothing alike in concept or execution. Also,
"type" is too vague to be a valid column name - "type" of
what? This is basics, damn it!!
xchange.
The logic of the join is that:
1-if all three fields [sic] match, Country, Exchange, and Type [sic]
then join those records [sic].
2-if two fields [sic] match, Country, and Exchange then join those
records [sic]. <
3-if one field [sic] matches, Country, then join those records [sic].
<<
Okay, try this:
SELECT ..
FROM Foo, Bar, etc.
WHERE CASE WHEN Foo.country_code= Bar.country_code
THEN 1 ELSE 0 END
+ CASE WHEN Foo.exchange_code = Bar.exchange_code
THEN 1 ELSE 0 END
+ CASE WHEN Foo.vague_type =Bar.vague_type
THEN 1 ELSE 0 END > 1 ;|||Terri
CREATE VIEW myView
AS
SELECT
OrderID, InstructionID,
I.Country ,I.Exchange ,I.Type FROM Orders O
JOIN Instructions I ON O.Country=COALESCE(I.Country,O.Country)
AND O.Exchange=COALESCE(I.Exchange,O.Exchange) AND
O.Type=COALESCE(I.Type,O.Type)
WHERE I.Exchange IS NOT NULL
--Final Select
SELECT OrderID,
InstructionID,
Country, Exchange, Type
FROM myView WHERE Type IS NOT NULL
UNION
SELECT OrderID,
InstructionID,
Country, Exchange, Type
FROM myView WHERE (SELECT COUNT(*) AS dp
FROM myView V WHERE OrderID=myView.OrderID ) =1
"Terri" <terri@.cybernets.com> wrote in message
news:dr3mjc$r6g$1@.reader2.nmix.net...
>I want to join 2 tables conditionally. One order needs to join with one
> instruction. The three potential join fields are: Country, Exchange, and
> Type. These fields are required in the Orders table but only Country is
> required in Instructions. The data entry requirements of the application
> are
> such that if an instruction has a Type it must have an Exchange.
> The logic of the join is that:
> 1-if all three fields match, Country, Exchange, and Type then join those
> records.
> 2-if two fields match, Country, and Exchange then join those records.
> 3-if one field matches, Country, then join those records.
> My expected results given the sample data is as follows.
> SELECT OrderID,InstructionID FROM Orders
> JOIN ...
>
> --Expected Results
> OrderID,InstructionID
> 1,1
> 2,5
> 3,5
> 4,7
> 5,8
> 6,9
> 7,13
> CREATE TABLE Orders
> (
> OrderID int NOT NULL,
> Country char (3)NOT NULL,
> Exchange char (3)NOT NULL,
> Type char (3)NOT NULL,
> )
> CREATE TABLE Instructions
> (
> InstructionID int NOT NULL,
> Country char (3) NOT NULL,
> Exchange char (3) NULL,
> Type char (3) NULL,
> Instructions varchar (15)NOT NULL
> )
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (1,'USA','NYS','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (2,'CAN','TSE','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (3,'CAN','TSE','Sel')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (4,'ESP','BAR','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (5,'ESP','MAD','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (6,'IRQ','BAG','Buy')
> INSERT Orders (OrderID,Country,Exchange,Type)VALUES (7,'DUE','HAM','Buy')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (1,'USA','NYS','Buy','Instruction 1')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (2,'USA','NYS','Sel','Instruction 2')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (3,'USA','NYS',NULL,'Instruction 3')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (4,'USA',NULL,NULL,'Instruction 4')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (5,'CAN','TSE',NULL,'Instruction 5')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (6,'CAN','ALB',NULL,'Instruction 6')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (7,'ESP',NULL,NULL,'Instruction 7')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (8,'ESP','MAD',NULL,'Instruction 8')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (9,'IRQ','BAG','Buy','Instruction 9')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (10,'IRQ','BAG','Sel','Instruction 10 ')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (11,'DUE',NULL,NULL,'Instruction 11')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (12,'DUE','HAM',NULL,'Instruction 12')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (13,'DUE','HAM','Buy','Instruction 13')
> INSERT Instructions
> (InstructionID,Country,Exchange,Type,Ins
tructions)VALUES
> (14,'DUE','HAM','Sel','Instruction 14')
>
>