DECLARE @team TABLE
(
id VARCHAR(20)
, team_des VARCHAR(20)
);
INSERT INTO @team ( id, team_des )
SELECT 'team_1', 'team_1_des' UNION ALL
SELECT 'team_2', 'team_2_des' UNION ALL
SELECT 'team_3', 'team_3_des' UNION ALL
SELECT 'team_4', 'team_4_des' UNION ALL
SELECT 'team_5', 'team_5_des' UNION ALL
SELECT 'team_6', 'team_6_des';
DECLARE @fantaincontro TABLE
(
id VARCHAR(20)
, fant_des VARCHAR(20)
, season VARCHAR(20)
, teamAway VARCHAR(20)
, teamHome VARCHAR(20)
);
INSERT INTO @fantaincontro ( id, fant_des, season, teamAway, teamHome )
SELECT 'fant_01', 'fant_01_des', 'seas_1', 'team_1', 'team_2' UNION ALL
SELECT 'fant_02', 'fant_02_des', 'seas_1', 'team_3', 'team_4' UNION ALL
SELECT 'fant_03', 'fant_03_des', 'seas_1', 'team_5', 'team_6' UNION ALL
SELECT 'fant_04', 'fant_04_des', 'seas_1', 'team_6', 'team_5' UNION ALL
SELECT 'fant_05', 'fant_05_des', 'seas_1', 'team_4', 'team_3' UNION ALL
SELECT 'fant_06', 'fant_06_des', 'seas_1', 'team_2', 'team_1' UNION ALL
SELECT 'fant_11', 'fant_11_des', 'seas_2', 'team_1', 'team_2' UNION ALL
SELECT 'fant_12', 'fant_12_des', 'seas_2', 'team_3', 'team_4' UNION ALL
SELECT 'fant_13', 'fant_13_des', 'seas_2', 'team_5', 'team_6' UNION ALL
SELECT 'fant_14', 'fant_14_des', 'seas_2', 'team_6', 'team_5' UNION ALL
SELECT 'fant_15', 'fant_15_des', 'seas_2', 'team_4', 'team_3' UNION ALL
SELECT 'fant_16', 'fant_16_des', 'seas_2', 'team_2', 'team_1';
SELECT f.id
, f.fant_des
, f.season
, f.teamAway
, (select team_des from @team t where t.id = f.teamAway ) teamAway_des
, f.teamHome
, (select team_des from @team t where t.id = f.teamHome ) teamHome_des
FROM @fantaincontro f
WHERE f.season LIKE '%seas_2%'
AND f.id LIKE '%fant_%'
ORDER BY f.id