Solved

SQL...Joining VIEWS

Posted on 2013-01-23
4
364 Views
Last Modified: 2013-01-23
I have multiple views, that I need to join together.
I am having a difficult time getting the data to appear like I want.


VIEW 1:

SELECT ORG_ID, ORGANIZATION_NAME, [GROUP], category_id, rpt_list_text, APRN, PA
  FROM vw_core_privileges_a16

ORG_ID	ORGANIZATION_NAME	GROUP	category_id	rpt_list_text	APRN	PA
1	       Northwestern Memorial Hospital	UHC	1	Write admission orders	1	1
2	       Rush University Medical Center	UHC	1	Write admission orders	1	1
3	       University of Chicago	                UHC	1	Write admission orders	1	1

Open in new window



VIEW 2:

SELECT ORG_ID, ORGANIZATION_NAME, [GROUP], category_id, rpt_list_text, APRN, PA
  FROM vw_core_privileges_a17

ORG_ID	ORGANIZATION_NAME	    GROUP	     category_id	rpt_list_text	APRN	PA
1	       Northwestern Memorial Hospital	UHC	    1	  Write discharge orders	   1	1
2	       Rush University Medical Center	UHC	    1	  Write discharge orders	   1	1
3	       University of Chicago	               UHC	    1 	  Write discharge orders	   1	1

Open in new window




I would like the data to appear like:

ORG_ID	ORGANIZATION_NAME	GROUP	category_id	rpt_list_text	APRN	PA
1	       Northwestern Memorial Hospital	UHC	    1	  Write admission orders	   1	  1
2	       Rush University Medical Center	UHC	    1	  Write admission orders	   1	  1
3	       University of Chicago	                UHC	    1	  Write admission orders	   1	  1
1	       Northwestern Memorial Hospital	UHC	    1	  Write discharge orders	   1	  1
2	       Rush University Medical Center	UHC	    1	  Write discharge orders	   1	  1
3	       University of Chicago	               UHC	    1 	  Write discharge orders	   1	  1

Open in new window

0
Comment
Question by:swaggrK
  • 2
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
Saurabh Bhadauria earned 250 total points
ID: 38810949
You need a union here ...

SELECT ORG_ID, ORGANIZATION_NAME, [GROUP], category_id, rpt_list_text, APRN, PA
  FROM vw_core_privileges_a16

union all

SELECT ORG_ID, ORGANIZATION_NAME, [GROUP], category_id, rpt_list_text, APRN, PA
  FROM vw_core_privileges_a17
0
 

Author Comment

by:swaggrK
ID: 38811021
Thank you, where is the best place to store this query, since it is not really a View?
0
 

Author Closing Comment

by:swaggrK
ID: 38811037
Excellent!!! Responded very quickly. Thank you.
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 38811047
ok then you can wrap your queries in a view...in  similar way
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 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