my tables are
t_location which is the physical location info such as address and phone
t_provider the contact information such as first and last names
t_source the person who refered that person
the only way i know how to do this now would be to query the location table get the locationid and add it to a dataset then do a search with each location id individually and the provider criteria but then i would not know how to get it to a dataset or something
i have seen the join statements but i dont know for sure how they work, would it be possible to use a join statement or something so that i could return the results so that i could later add them to a table.
if this is possible could you give me the basic structure for the join command and if you use a join statement do you use the select command in the same statement or do you run a seperate statement?
thank you for everything that you can giveThere are several types of joins...
inner - only return rows where there is data in all tables
outer - return all the data from the primary table and any linked data from the lookup tables
Here's an example of an inner join using the Northwind database
Say you wanted to know, for each product, what city it was shipped to, how many, and when.
SELECT Products.ProductName, Orders.ShipCity, [Order Details].Quantity, Orders.ShippedDate
FROM Products
INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
INNER JOIN Orders ON Orders.OrderID = [Order Details].OrderID
ORDER BY ProductName
This returns the following...
ProductName ShipCity Quantity ShippedDate
------------ ----- --- ----------------
Alice Mutton Strasbourg 30 1996-08-12 00:00:00.000
Alice Mutton Frankfurt a.M. 15 1996-08-16 00:00:00.000
Alice Mutton Albuquerque 15 1996-09-05 00:00:00.000
Alice Mutton Charleroi 40 1996-10-09 00:00:00.000
.
.
.
If theres a product in the database that hasn't shipped at all, it wont show up in the results at all.
If you want it in the list, you would do an outer join...
SELECT Products.ProductName, Orders.ShipCity, [Order Details].Quantity, Orders.ShippedDate
FROM Products
LEFT OUTER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID
LEFT OUTER JOIN Orders ON Orders.OrderID = [Order Details].OrderID
ORDER BY ProductName
In this case, the LEFT OUTER JOIN operator sells SQL that you want all the data from the table on the left and null for any fields in the table on the right that dont have a related record. Like so...
ProductName ShipCity Quantity ShippedDate
------------ ----- --- ----------------
Alice Mutton Strasbourg 30 1996-08-12 00:00:00.000
Alice Mutton Frankfurt a.M. 15 1996-08-16 00:00:00.000
Alice Mutton Albuquerque 15 1996-09-05 00:00:00.000
Alice Mutton Versailles 6 1998-03-26 00:00:00.000
.
.
.
Alice Mutton Rio de Janeiro 12 NULL
Alice Mutton Boise 77 1998-05-04 00:00:00.000
Aniseed Syrup London 30 1996-08-28 00:00:00.000
.
.
.
For your case, you might want to use an outer join since you would want to know if a given location is in the database but doesn't have any provider or source.
For example...
|||thank you this will help out alot|||alright here is an sql statement i am using
SELECT address, phone, first, last, refereanceperson
FROM t_location
LEFT OUTER JOIN t_provider on t_location.locationid=t_t_provider.locationid
LEFT OUTER JOIN t_source ON t_provider.sourceid=t_source.sourceid
SELECT t_Provider.ProviderID
FROM t_Location INNER JOIN
t_Provider ON t_Location.LocationID = t_Provider.LocationID INNER JOIN
t_Source ON t_Provider.ProviderID = t_Source.ProviderID
WHERE (t_Location.StateID = 25)
and currently i have 2 rows in my location table with the stateID of 25 and 2 of the providers have the same location id so it should bring up 3
providerid location id
112 147
151 147
114 149
but it brings up three and did before i added the center record too
and their values are
112
114
112
why is it bringing up 2 of the same and why does it ignore when i add another record
by the way the location id is the one with the stateID column|||I'm confussed. Can you post the table contents?|||i changed it to left outer join and it got all of the fields but it still duplicates the 112 provider id
the table contents are
t_Provider
ProviderIDDoNotCallInProcessLocationIDProviderContactProviderEmailProviderFNameProviderLName
11200147Natasha SmithJosephStephens@.DentistPro.comJosephStephens
11300148JodiKbroadbridge@.TeNT.comKurtBroadbridge
11400149Casey HuntJon_nedry@.newparadox.comJonathanNedry
11500150CoorsABush@.HHD.comAnheiserBush
11600151Jonathan NedryDSpeed@.AMD.comDoctorSpeed
11700147Bob Stephenshowdy@.me.comMEToo
11800152Mr Wallaceashd@.ld.comGuySmiley
t_Location
LocationIDLocationAddress1LocationAddress2LocationCityLocationDoctorsLocationFaxLocationONameLocationPhoneLocationZipStateID
1474832 Dixie HwySuite 105Waterford1248-313-4038 Dentistry Professionals248-313-4039 48038 25
14894949 Sunshine DriveOthello1Teeth N' Toothpaste234-343-2343 54343 7
149421 East OtisHazel Park1New Paradox Cleaning248-543-1983 48909 25
150148392 Small Back Rd.St. Lous1Happy Hour Dental243-333-2222 83939 26
151123 Fastest CPU Ln.Extreme Performance1AMD Dental239-343-3234 39393 22
152125 Tanglewood Tr.Ortonville1Mr.Dental248-627-46813495025
t_Source
SourceIDAdminIDCompetitorIDGroupIDLeadSourceIDPatientProviderIDSourceDate
15361641123/4/2004
15415131133/4/2004
1551112Dan Guzek1143/4/2004
15615131153/4/2004
15731541163/4/2004
15814131123/4/2004
New Sql Satement
SELECT t_Provider.ProviderID
FROM t_Location LEFT OUTER JOIN
t_Provider ON t_Location.LocationID = t_Provider.LocationID LEFT OUTER JOIN
t_Source ON t_Provider.ProviderID = t_Source.ProviderID
WHERE (t_Location.LocationPhone <> '') AND (t_Location.StateID = 25)
New Returned Values
ProviderID
112
112
117
114
118
sorry if the tables dont come out correctly they are a bit bigger than the text field|||Ok it looks like it did this because i had two source records in the database which i would like to have anyway but would not like the providerto show up everytime there is a source for the provider is this possible?|||oh yeah and one more thing
1 to many
location to provider
provider to source
i dont know if this would help a solution at all|||I'm still confused on exactly what you want to get back.|||I would like it to only get back the providersID's that match ONLY once
but it seems to give me everything correctly except if there is more than one source
if there is more than one source for a provider id it returns the provider id more than once|||The providerIDs that match what?
Are you saying you want a list of the providerIDs that have only one source? Or do you want a list of the providerIDs along with their sources but only the first incident of each provider (i.e. provider 112 has two sources (153 and 158), so only return the 112/153 pair and filter out the 112/158 pair?|||in my search i searched for the state 25 and it came back with the provider id of 112 twice because there were two sources but it should not have done that because the source criteria isnt part of the search|||OK. Lets cover the basics of a join.
A join is a cross product.
For example, say you hve the following tables...
AlphaTable
AlphaID AlphaValue
--- ----
1 A
2 B
3 CPrimayTable
ID Name AlphaID
--- --- ---
1 Bill 2
2 Steve 1
3 John 3
4 Bob 2
If you joined them on the AlphaID you would get...
ID Name AlphaID AlphaValue
--- -- ---- ----
1 Bill 2 B
2 Steve 1 A
3 John 3 C
4 Bob 2 B
When you select from a join, you are basically selecting from a virtual table containing all the columns and all the joined data. So, if you select just the AlphaValue from this join, B is in there twice. Even though you are just selecting the AlphaValue, and B is only in the AlphaTable once, since you are selecting from the join, B is going to be in there twice.|||so basically then when you complete a join it is like having 3 seperate tables with all the same values where they are using the same records it makes duplicates
where the break occurs.
For instance
my table structure is as follows
location expands to providers expands to source
1 to many
1 to many
1 to many
so anything when searching on the provider table with present duplicates of the source table
so if i searched from the location table and i had 2 providers that used the same location i would have 2 duplicates and then each of those two providers had 2 sources
then i would have 4 duplicate Location ID's i believe if i did the math correctly?
| Location
/ \
/ \
/\ /\ Providers
/ \ / \Sources
if i am correct in assuming this from the facts that you have given me this is going to be a pain.
is there any way to limit the results to non duplicates in the sql command?|||You've got it exactly.
Yes, there are ways to limit the output, but these records are not "duplicates". Yes, the location will be listed multiple times if there are multiple providers at that location, but the rest of the result (i.e. the provider information) is different. The question to answer is "what information do you want?" If you are building a select that returns the location and provider (ignoring sources for the moment), and a given location has two providers, do you want to show the location and the first provider for that location, and filter out all the other providers at that location? Or do you want to show each location and a concatenated string of all the providers at that location? or something else?
No comments:
Post a Comment