Solved

SQL Combination Tables

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

Command Line Tips and Tricks

The command line is a powerful tool at the disposal of every Linux user. Although Linux distros come with beautiful user interfaces, it's worthwhile to learn the command line because it allows you to do a number of things that you otherwise cannot do from the GUI.  

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

622 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