asked on

SQL Combination Tables

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.

COANetwork

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

robthomas09

ASKER

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

ASKER CERTIFIED SOLUTION

COANetwork

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

robthomas09

ASKER

That works fantastic. Thanks COANetwork!