Joining two tables on multiple criteria I have two tables products and positions. 

products:

id BIGINT PRIMARY KEY,
product VARCHAR(100),
criterium1 VARCHAR(100),
criterium2 VARCHAR(100)


positions:

id BIGINT PRIMARY KEY,
position VARCHAR(100),
criterium1 VARCHAR(100),
criterium2 VARCHAR(100)


Each table has criterium1 and criterium2.  I would like to have a result set containing joins between positions and products.  For each position I would like the corresponding product that matches either on criterium1 or criterium2; when criterium1 cannot be found or is NULL, it needs to match on criterium2.

What would the SQL statement look like?

So far I have tried:

SELECT * FROM positions pos 
INNER JOIN products pro 
ON COALESCE(pos.criterium1, pos.criterium2,'') 
    = COALESCE( pro.criterium1, pro.criterium2,'') 


However, this does not match pro.criterium1 to pos.criterium1 or pro.criterium2 to pos.criterium2.

Addition:
Sorry my requirements are a little murky. Let me try to complete paint the picture.

We are using SQL Server 2005.

products:

id   product   criterium1   criterium2
 1    pro1       AAA1         910
 2    pro2       106          BB2
 3    pro3       AB1          XXY

positions:

id  position  criterium1  criterium2
 1    pos1       NULL         910
 2    pos2       106          CCC
 3    pos3       XXX          BB2
 4    pos4       AA1          XXY
 5    pos5       NULL         123


What I am looking for is table joining using conditional matching, where I would like to match positions to products.  First I want to match on criterium1.  If I cannot find a match on criterium1 or the position criterium1 is NULL, I need to match on criterium2.

In the above example the expected result set would be:

resultset
positionId  productId
 1            1 (no match on criterium1 since NULL, match on criterium2 )
 2            2 (match on criterium1)
 3            2 (no match on criterium1, match on criterium2 )
 4            1 (match on criterium1, criterium2 match is disregarded)


I only want to match positions.criterium1 with products.criterium1 and positions.criterium2 with products.criterium2.
Assuming pro1, criterium1 should be AA1, this produces the results you are looking for:

SELECT pos.id, pro.id, pos.criterium1, pos.criterium2
   , pro.criterium1, pro.criterium2 
FROM Positions pos
JOIN Products pro ON pos.criterium1 = pro.criterium1 
   OR (pos.criterium2 = pro.criterium2
   AND pos.id NOT IN (
      SELECT posx.id FROM Positions posx
      JOIN Products prox ON posx.criterium1 = prox.criterium1
   ))
ORDER BY 1;


Test query (only tested in Oracle):

WITH Products AS (
   SELECT 1 id, 'AA1' criterium1, '910' criterium2 FROM dual UNION ALL
   SELECT 2 id, '106'  criterium1, 'BB2' criterium2 FROM dual UNION ALL
   SELECT 3 id, 'AB1'  criterium1, 'XXY' criterium2 FROM dual
   ),
   Positions AS (
   SELECT 1 id, NULL  criterium1, '910' criterium2 FROM dual UNION ALL
   SELECT 2 id, '106' criterium1, 'CCC' criterium2 FROM dual UNION ALL 
   SELECT 3 id, 'XXX' criterium1, 'BB2' criterium2 FROM dual UNION ALL 
   SELECT 4 id, 'AA1' criterium1, 'XXY' criterium2 FROM dual UNION ALL 
   SELECT 5 id, NULL  criterium1, '123' criterium2 FROM dual    
   )   
SELECT pos.id, pro.id, pos.criterium1, pos.criterium2
   , pro.criterium1, pro.criterium2 
FROM Positions pos
JOIN Products pro ON pos.criterium1 = pro.criterium1 
   OR (pos.criterium2 = pro.criterium2
   AND pos.id NOT IN (
      SELECT posx.id FROM Positions posx
      JOIN Products prox ON posx.criterium1 = prox.criterium1
   ))
ORDER BY 1;