I have two tables products and positions.
id BIGINT PRIMARY KEY, product VARCHAR(100), criterium1 VARCHAR(100), criterium2 VARCHAR(100)
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
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