Joining two tables on multiple criteria

Question

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.

Answer 1

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;

Answer 2

Not sure I get what you're aiming at, but this might get you farther down the road. (PostgreSQL).

select * 
from positions pos 
inner join products pro 
on case when pos.criterium1 is not null then pos.criterium1 = pro.criterium1 
        when pos.criterium1 is null     then pos.criterium2 = pro.criterium2 
   end

Answer 3

I know this post is old, but I was looking for a similar question. Would this work

SELECT coalesce(pos.id, pro.id)
   ,coalesce(criterium1, pos.criterium2)
   ,coalesce(pro.criterium1, pro.criterium2)
FROM positions pos 
 LEFT JOIN products pro 
   ON pos.criterium1 = pro.criterium1
 LEFT JOIN products pro2
   ON pos.criterium2 = pro2.criterium2

Since coalesce returns the first non null value, if criterium1 matches it will ignore criterium2.