Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
What have you tried? –  JNK Dec 21 '11 at 18:26
    
I tried: SELECT * FROM positions pos INNER JOIN products pro ON COALESCE(pos.criterium1, pos.criterium2,'') = COALESCE( pro.criterium1, pro.criterium2,'') but this does not match pro.criterium1 to pos.criterium1 or pro.criterium2 to pos.criterium2 –  HenkieR Dec 21 '11 at 18:57
2  
Welcome to DBA.se. Your questions will typically get better answers and more votes if you include information on what you have already tried, which is why JNK asked. I'm editing your response into the question. Also, if your question is for a specific database server (e.g. Oracle, SQL Server) it is a good idea to put that in the question and in the tags. Which RDBMS are you targeting? –  Jonathan Van Matre Dec 21 '11 at 20:23
1  
Some example data might help. Performance of this JOIN will likely suck as it is completely unsargable. What RDBMS are you using? –  Martin Smith Dec 21 '11 at 20:42
1  
How can pos4 (AA1,XXY) match pro1 (AAA1, 910) on criterium1? Did you mean pro1 to be (AA1, 910)? –  Leigh Riffel Dec 22 '11 at 14:34
show 2 more comments

2 Answers 2

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;
share|improve this answer
    
it works swimmingly. Thanks a lot! –  HenkieR Dec 22 '11 at 15:53
2  
Glad to help. The way to say thanks on this site is to up-vote the answer by clicking the up arrow next to it. If the response satisfactorily answers your question then you should also check the check-mark next to the answer. See dba.stackexchange.com/faq#reputation for more info. –  Leigh Riffel Dec 22 '11 at 16:40
2  
+1 for being able to decipher that question. –  BryceAtNetwork23 Dec 22 '11 at 16:49
add comment

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.