Solved

Query

Posted on 2014-11-21
13
98 Views
Last Modified: 2014-12-08
Hi,

I have a query that result in this

 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  



I need a query that gives me this output:

AppID: 
35
554
436
538
542
185
123
2048
120
2049

Open in new window

0
Comment
Question by:lulu50
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
13 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 40457944
Try this:
SELECT DISTINCT APPID
FROM (SELECT APPLICATIONID AS APPID FROM MyTable UNION ALL
      SELECT TO_APPID AS APPID FROM MyTable) t1

Open in new window

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

Expert Comment

by:Jim Horn
ID: 40457958
<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

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40457968
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.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40457989
is order important?
0
 

Author Comment

by:lulu50
ID: 40457990
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  

Open in new window

0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40457997
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

Open in new window

0
 

Author Comment

by:lulu50
ID: 40458004
I am confused????
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40458012
:) 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 

Open in new window

0
 
LVL 20

Expert Comment

by:dsacker
ID: 40458031
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.
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40458037
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...
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40458946
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
;

Open in new window

0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40461677
If you don't mind to have a row order column in the result then you can try this solution:
WITH App_CTE( AppID, RowID) AS 
(
	SELECT AFW.ApplicationID, ROW_NUMBER() OVER(ORDER BY AFW.ApplicationName, AR.ApplicationName ) AS Row
	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 
	UNION ALL
	SELECT AFW.AppID_To, ROW_NUMBER() OVER(ORDER BY AFW.ApplicationName, AR.ApplicationName ) AS Row
	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 
)
SELECT AppId, MIN(RowID)
FROM App_CTE
GROUP BY AppID
ORDER BY 2, 1

Open in new window

0
 

Author Closing Comment

by:lulu50
ID: 40486639
Thank you
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Export Data from MySql Using PHP 16 62
WordPress  Failed to Import Media 8 59
Trying to understand why my Index is so large 12 48
Need a mirrored QA test site 2 86
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question