Solved

SQL Combination Tables

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

744 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now