Query

Try this:

SELECT DISTINCT APPID
FROM (SELECT APPLICATIONID AS APPID FROM MyTable UNION ALL
      SELECT TO_APPID AS APPID FROM MyTable) t1

Select allOpen in new window

Obviously, replace "MyTable" with whatever really is your table name. Good luck.

<very wild guess based only on the above set>

SELECT a.value 
FROM (
   SELECT APPLICATIONID as value, TO_APPNAME, 1 as sort_order
   FROM YOUR_TABLE
   UNION ALL
   SELECT TO_APPID as value, TO_APPNAME, 2 as sort_order
   FROM YOUR_TABLE) a
ORDER BY a.TO_APPNAME, a.sort_order

Select allOpen in new window

Looks like we cross-posted, sorry.  Please try both out, as we're not connected to your source of data so we can't test, giving dsacker's T-SQL priority as he posted first.

is order important?

this query get me 

SELECT AFW.ApplicationID, AFW.ApplicationName, AFW.AppID_To AS To_AppID, AR.ApplicationName AS To_AppName 
FROM [dbo].Application_From_View AFW LEFT OUTER JOIN [dbo].AppRepository AR ON (AFW.AppID_To=AR.ApplicationID) 
WHERE AR.ApplicationName is not null and AR.retired=0 
ORDER BY AFW.ApplicationName, AR.ApplicationName 

get me this output


APPLICATIONID    APPLICATIONNAME      TO_APPID            TO_APPNAME 
35                               test0                              554                           Name1  
436                             test 1                                538                        Name2  
436                            test2                                542                        Name3    
436                            test3                                185                       Name4   
436                            test4                                123                       Name5  
2048                         test5                                 120                       Name6    
2048                        test6                                 2049                      Name7  

Select allOpen in new window

i guess if it the order is not important, here it is

select ApplicationID from [dbo].Application_From_View
union
select AppID_To from [dbo].Application_From_View

Select allOpen in new window

I am confused????

:) to be confused is normal... and you are confusing us by adding more info on every post, which changes the solutions...

try this, as is:

select AFW.ApplicationID  from (
	select ApplicationID from [dbo].Application_From_View
	union
	select AppID_To from [dbo].Application_From_View
) AFW inner join [dbo].AppRepository AR ON AFW.ApplicationID=AR.ApplicationID AND AR.retired=0 

Select allOpen in new window

Considering the progressive revelations of the table(s) involved, if you take HainKurt's suggestion, simply change it to:

select DISTINCT AFW.ApplicationID ...

And since the desired results are sorted, add:

ORDER BY ApplicationID

... at the bottom.

UNION eliminates duplicates, so no need for "DISTINCT"

agreed on second part, an order is always useful on application side... should always add some sort to queries, unless it is absolutely not necessary or creating performance issues...

Depending on what that view is doing, there may be no need for multiple queries and hence no need for either UNION or DISTINCT.

Please try this:

SELECT
      AR.ApplicationID
FROM [dbo].AppRepository AR
WHERE AR.retired = 0
ORDER BY
      AR.ApplicationID
;

Select allOpen in new window

Thank you