1. solution
SELECT t1.id, [flag] = CASE
WHEN companyid=1 THEN 'c'
WHEN companyid=2 THEN 'd'
WHEN companyid=3 THEN 'e'
WHEN companyid=4 THEN 'f'
WHEN companyid=5 THEN 'g'
ELSE 'h'END
FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id=t2.id
More about:
CASE (T-SQL)[
^]
2. solution:
SELECT T.*
FROM (
SELECT t1.id, 'c' AS [flag]
FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id=t2.id
WHERE companyid = 1
UNION ALL
SELECT t1.id, 'd' AS [flag]
FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id=t2.id
WHERE companyid <> 1 AND IntField=5
UNION ALL
SELECT t1.id, 'c' AS [flag]
FROM table1 AS t1 INNER JOIN table2 AS t2 ON t1.id=t2.id
WHERE companyid = 1 AND IntField=9 AND TextField='CP'
) AS T
More about:
UNION (T-SQL)[
^]