-- formatted original
select distinct
t1.ServerName,
t1.[Application ID],
t1.[Application Name],
t2.VP1,
T2.VP2,
T2.SVP,
T2.[DC2015 Move Group],
T1.[System Type],
T3.Line,
-- defines 1 column
AppServerSource = (
Select -- returns 3 columns
IsNull(t1.ServerName,t2.ServerName) as ServerName,
IsNull(t1.[Application Name],t2.[Application Name]) as [Application Name],
Case
When t1.[Application Name] is null then 'Check'
When t2.[Application Name] is null then 'Payorder'
Else 'Both'
End AppServer)
from Table1 t1
INNER JOIN Table2 t2
on t1.[Application ID]= t2.[Application ID]
and t1.Servername = t2.ServerName
JOIN BIM T3
ON t1.[Application Name]= t3.[Application Name]
order by Servername -- this is ambiguous and will error - use a table alias
-- alternative
select distinct
t1.ServerName,
t1.[Application ID],
t1.[Application Name],
t2.VP1,
T2.VP2,
T2.SVP,
T2.[DC2015 Move Group],
T1.[System Type],
T3.Line,
ServerName = IsNull(t1.ServerName,t2.ServerName),
[Application Name] = IsNull(t1.[Application Name],t2.[Application Name]),
AppServer = Case
When t1.[Application Name] is null then 'Check'
When t2.[Application Name] is null then 'Payorder'
Else 'Both'
End
from Table1 t1
INNER JOIN Table2 t2
on t1.[Application ID]= t2.[Application ID]
and t1.Servername = t2.ServerName
INNER JOIN BIM T3
ON t1.[Application Name]= t3.[Application Name]
order by t1.Servername
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden