Solved

SQL Combination Tables

Posted on 2013-10-23
4
137 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
  • 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

825 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