Solved

SQL Combination Tables

Posted on 2013-10-23
4
151 Views
Last Modified: 2013-10-25
Hello Experts,

I need to develop scripts to generate a few tables that are combined from other tables. I have a table containing providers and a table containing locations.

Provider           Uniq_ID
____________      _________
Test 1, John	826D3EB6-4198-4202-8B5A-B4541BE0F2F7
Test 2, Jane	AE3D195E-DF03-4C92-A5FD-8E02B438E318
Tester, Jim       6AA0CA8F-B8AF-4B3D-87B0-FDD5BF35F096

Open in new window


Location                                  Uniq_Id
_________                                  ___________
Anderson Internal Medicine	14BC4AB9-6A8F-497E-B5CB-37C7BAE1AB66
Baptist Medical Center            B9C4053D-A87E-494B-AA68-37EBC26C6894
Christian Children's Hospital	4CFAB23F-40AF-4E65-9832-757B0D93D0B6
Memorial Hospital	                C7326B3F-ED69-4065-B077-DC8E2D7B5D05

Open in new window


I'm looking to possibly curse through and develop scripts to create new tables that look similar to the attached. The document column will be hard coded with a value.

I am basically looking for a table generating script that can be made with all locations for one provider, all providers with one location, and one with all locations and all providers. Hopefully that makes sense.
0
Comment
Question by:robthomas09
[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
  • 2
  • 2
4 Comments
 
LVL 9

Expert Comment

by:COANetwork
ID: 39595753
This makes little sense.  You mention a "document" column that does not appear in your examples.  And you did not provide any sample output of what you want.
I'll take a guess:  If you want to return a recordset containing all rows from Provider, and for each provider have all locations (provider values duplicated in resultset, unique provider/location combinations) then you would use something like this:
SELECT Provider.Provider, Location.Location FROM Provider LEFT JOIN Location on 1 = 1

Open in new window

If you want to return a recordset containing all rows from Location, and for each location have all providers (location values duplicated in resultset, unique provider/location combinations) then you would use something like this:
SELECT Provider.Provider, Location.Location FROM Provider RIGHT JOIN Location on 1 = 1

Open in new window

If you want every record in Provider to be matched with every record in Location (non-unique provider/location combinations) then you would use
SELECT Provider.Provider, Location.Location FROM Provider CROSS JOIN Location

Open in new window

first sample will give you results like
1 1
1 2
1 3
2 1
2 2
2 3
Second sample will give you results like
1 1
2 1
3 1
1 2
2 2
3 2
Third sample will give you results like
1 1
2 1
3 1
1 2
2 2
3 2
3 1
3 2
3 3
0
 

Author Comment

by:robthomas09
ID: 39597350
My apologies COANetwork,

I thought i had attached an excel file showing what i was looking for in a result table. The Document column in these tables will be hard coded with a value.
Destination-Tables.xlsx
0
 
LVL 9

Accepted Solution

by:
COANetwork earned 500 total points
ID: 39597942
/***
Setting up my test data
***/
SET NOCOUNT ON
CREATE TABLE #Location (Location nvarchar(50) NULL);
CREATE TABLE #Provider (Provider nvarchar(50) NULL);

INSERT INTO #Location (Location) VALUES('Anderson Internal Medicine');
INSERT INTO #Location (Location) VALUES('Baptist Medical Center');
INSERT INTO #Location (Location) VALUES('Christian Children''s Hospital');
INSERT INTO #Location (Location) VALUES('Memorial Hospital');

INSERT INTO #Provider (Provider) VALUES('Alexander, John');
INSERT INTO #Provider (Provider) VALUES('Brown PA, Maureen');
INSERT INTO #Provider (Provider) VALUES('Cook, MD, Charles');

/***
Begin code to extract data into new tables
***/
SELECT #Provider.Provider, #Location.Location, 'Chart_Note' AS Document
INTO #AllProvidersForLocationTable --Table gets created on the fly with same columns as source
FROM #Location WITH(NOLOCK)
LEFT JOIN #Provider WITH(NOLOCK) ON 1 = 1 -- forces every row from provider to be returned for each location row
WHERE #Location.Location = 'Anderson Internal Medicine' -- optional - limits results to only 1 specific location

SELECT #Provider.Provider, #Location.Location, 'Chart_Note' AS Document
INTO #AllLocationsForProviderTable --Table gets created on the fly with same columns as source
FROM #Location WITH(NOLOCK)
RIGHT JOIN #Provider WITH(NOLOCK) ON 1 = 1 -- forces every row from location to be returned for each provider row
WHERE #Provider.Provider = 'Alexander, John' -- optional - limits results to only 1 specific provider

SELECT #Provider.Provider, #Location.Location, 'Chart_Note' AS Document
INTO #AllLocationsAllProvidersTable --Table gets created on the fly with same columns as source
FROM #Location WITH(NOLOCK)
CROSS JOIN #Provider WITH(NOLOCK) --each row from location is matched to each row from provider (ON keyword is not valid for this type of join)
-- WHERE clause is optional, if you want to limit either providers or locations.

/***
End code to extract data into new tables

Begin code to select extracted data
***/

SELECT * FROM #AllProvidersForLocationTable;
SELECT * FROM #AllLocationsForProviderTable;
SELECT * FROM #AllLocationsAllProvidersTable;

/***
End code to extract data into new tables

Begin test data cleanup
***/

DROP TABLE #AllProvidersForLocationTable
DROP TABLE #AllLocationsForProviderTable
DROP TABLE #AllLocationsAllProvidersTable
DROP TABLE #Location
DROP TABLE #Provider
SET NOCOUNT OFF
GO

Open in new window

0
 

Author Closing Comment

by:robthomas09
ID: 39601134
That works fantastic. Thanks COANetwork!
0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

710 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