Returning Result Based on 2 conditions from one Table Problem Statement: I have a table that holds three pieces of information (Number, Id, Type). Let’s call it table A:

╔══════╦════╦══════╗
║Number║ Id ║Type  ║
╠══════╬════╬══════╣
║ 1234 ║1111║Test 1║
║ 1234 ║2222║Test 1║
║ 1234 ║3333║Test 2║
║ 1234 ║4444║Test 2║
╚══════╩════╩══════╝


I have another table that holds 4 pieces of information (Number, Test1, Test2, ID). Let’s call it table B:

╔══════╦═════╦══════╦══════════════════╗
║Number║Test1║Test 2║ ID               ║
╠══════╬═════╬══════╬══════════════════╣
║ 1234 ║1111 ║      ║a1v1b0000006lmMAAQ║
║ 1234 ║2222 ║      ║a1v1b0000006lmNAAQ║
║ 1234 ║     ║3333  ║a1vf1000000qvTYAAY║
║ 1234 ║     ║4444  ║a1vf1000000qvTiAAI║
╚══════╩═════╩══════╩══════════════════╝


What I would like to do is, based on Number and Type from Table A, to search the Table B and return the corresponding ID.

The desired outcome is as follows:

╔══════╦════╦══════╦══════════════════╗
║Number║ ID ║Type  ║ ID               ║
╠══════╬════╬══════╬══════════════════╣
║ 1234 ║1111║Test 1║a1v1b0000006lmMAAQ║
║ 1234 ║2222║Test 1║a1v1b0000006lmNAAQ║
║ 1234 ║3333║Test 2║a1vf1000000qvTYAAY║
║ 1234 ║4444║Test 2║a1vf1000000qvTiAAI║
╚══════╩════╩══════╩══════════════════╝


NB: I have tried to use a decode but reading up on it, I am getting mixed signals whether this is the correct approach.
This is a quick solution that uses union all.  

The first query finds the matching rows between Table1 and Table2 where Table1.Type = 'Test 1' and Table1.Id = Table2.[Test 1]

The second query finds the matching rows between Table1 and Table2 where Table1.Type = 'Test 2' and Table1.Id = Table2.[Test 2]

--demo setup
Declare @Table1 table (Number int, ID int, Type varchar(10))
INSERT INTO @Table1
  (Number, Id, Type)
VALUES
  (1234, 1111, 'Test 1'),
  (1234, 2222, 'Test 1'),
  (1234, 3333, 'Test 2'),
  (1234, 4444, 'Test 2');


Declare @Table2 table (Number int, [Test 1] varchar(10), [Test 2] varchar(10), ID varchar(20))
INSERT INTO @Table2
  (Number, [Test 1], [Test 2], ID)
VALUES
  (1234, '1111', '', 'a1v1b0000006lmMAAQ'),
  (1234, '2222', '', 'a1v1b0000006lmNAAQ'),
  (1234, '', '3333', 'a1vf1000000qvTYAAY'),
  (1234, '', '4444', 'a1vf1000000qvTiAAI');

--solution using union all
SELECT t1.Number
    ,t1.ID
    ,t1.Type
    ,t2.ID
FROM @Table1 t1
JOIN @Table2 t2
    ON t1.Type = 'Test 1'
        AND t2.[Test 1] = t1.ID

UNION ALL

SELECT t1.Number
    ,t1.ID
    ,t1.Type
    ,t2.ID
FROM @Table1 t1
JOIN @Table2 t2
    ON t1.Type = 'Test 2'
        AND t2.[Test 2] = t1.ID




| Number | ID   | Type   | ID                 |
|--------|------|--------|--------------------|
| 1234   | 1111 | Test 1 | a1v1b0000006lmMAAQ |
| 1234   | 2222 | Test 1 | a1v1b0000006lmNAAQ |
| 1234   | 3333 | Test 2 | a1vf1000000qvTYAAY |
| 1234   | 4444 | Test 2 | a1vf1000000qvTiAAI |