Solved

SQL...Joining VIEWS

Posted on 2013-01-23
4
365 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

837 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