CODEWITHSUNDEEP
sql-servert-sql
Beems
Beems

Reputation: 810

T-SQL: How Can I JOIN Each Row Differently

How can I join two tables together that have dissimilar fields and require different rules to JOIN? An example:

tb_references               tb_parts
==================          ====================
f_type  |  f_item           f_partID  |  f_partnumber
------------------          --------------------
YY      |  ABC123           1         |  YYABC123
YY      |  ABC124           2         |  YYABC124
NN      |  000111           3         |  YY000111
NN      |  000112           4         |  YY000112

To JOIN these, I would normally perform a query like the following

SELECT * 
FROM tb_references
JOIN tb_parts
ON f_type + f_item = f_partnumber

However, you can see where the f_type of the NN won't match, so my results would look like the following:

RESULTS
=============================================
f_type  | f_item  | f_partID  | f_partnumber
---------------------------------------------
YY      | ABC123  | 1         | YYABC123
YY      | ABC124  | 2         | YYABC124

How can I specify an IF statement in the query to allow me to match the broken NN parts as well?

Something like this logic, but I can't figure out how to accomplish it though I've tried various methods without success:

SELECT * 
FROM tb_references
JOIN tb_parts
ON 
 IF (
    f_type = 'NN' THEN
      'YY' + f_item = f_partnumber
    )
 ELSE (
    f_type + f_item = f_partnumber
 )

My optimal results, then, would be like this:

RESULTS
=============================================
f_type  | f_item  | f_partID  | f_partnumber
---------------------------------------------
YY      | ABC123  | 1         | YYABC123
YY      | ABC124  | 2         | YYABC124
NN      | 000111  | 3         | YY000111
NN      | 000112  | 4         | YY000112

I'm open, of course, to other solutions that don't utilize an IF in the JOIN - it is just my way to try to describe the problem I'm facing. Unfortunately, I can't simply update the "f_type" field because it's a third-party database that we cannot control nor edit.

Upvotes: 0

Views: 43

Answers (2)

JoYi
JoYi

Reputation: 132

If the prefix for f_partnumber is always 2 in length then you can just replace those with an empty character in the join.

SELECT DISTINCT * 
FROM tb_references r
JOIN tb_parts p ON r.f_item = REPLACE(f_partnumber, LEFT(p.f_partnumber, 2), '')

Upvotes: 0

Wouter
Wouter

Reputation: 2976

You can use a CASE statement. Could be a pain performance wise, though:

SELECT * 
FROM tb_references
JOIN tb_parts
ON CASE WHEN f_type='yy' THEN 'zz' ELSE f_type END  + f_item = f_partnumber

Upvotes: 1

Related Questions