Emailing long messages from SQL2000

Hello!
The problem is this.

I have many short messages in a table. I need to tigh them together in one long email message and email to the users.
But xp_sendmail is limited to 7,790.
How could I send longer messages? Or how could I devide the long message and send it in parts (i.e. separate consequtive emails)?

This doesn't work, it still cuts the messages off at around 7,790:

E. Send messages longer than 7,990 characters

This example shows how to send a message longer than 7,990 characters. Because message is limited to the length of a varchar (less row overhead, as are all stored procedure parameters), this example writes the long message into a global temporary table consisting of a single text column. The contents of this temporary table are then sent in mail using the @.query parameter.

CREATE TABLE ##texttab (c1 text)

INSERT ##texttab values ('Put your long message here.')

DECLARE @.cmd varchar(56)

SET @.cmd = 'SELECT c1 FROM ##texttab'

EXEC master.dbo.xp_sendmail 'robertk',

@.query = @.cmd, @.no_header= 'TRUE'

DROP TABLE ##texttab

you can output the query to a file

then have it as an attachment

|||

Thank you very much for the quick response!
I just tried using @.attach_results with xp_sendmail, it properly created a file and emailed it, but the text in the file was still cut off at the same point as before.
Is there any any method of outputting a query to a file?

Thanks again!

|||

hi,

im referrring to this not the other one

xp_sendmail {[@.recipients =] 'recipients [;...n]'}
[,[@.message =] 'message']
[,[@.query =] 'query']
[,[@.attachments =] 'attachments [;...n]']
[,[@.copy_recipients =] 'copy_recipients [;...n]'
[,[@.blind_copy_recipients =] 'blind_copy_recipients [;...n]'
[,[@.subject =] 'subject']
[,[@.type =] 'type']
[,[@.attach_results =] 'attach_value']
[,[@.no_output =] 'output_value']
[,[@.no_header =] 'header_value']
[,[@.width =] width]
[,[@.separator =] 'separator']
[,[@.echo_error =] 'echo_value']
[,[@.set_user =] 'user']
[,[@.dbuse =] 'database']

Arguments

[@.attachments =] 'attachments [;...n]'

Is a semicolon-separated list of files to attach to the mail message.

hope it helps.

regards,

joey

|||Yes, the problem is I don't know how to output data from a stored procedure to a file...|||

use bcp.

call bcp from xp_cmdshell

|||

you could use a Job to move your email into a file and later, send your email.

is it work for you ?

Regards.

|||Did you try some code similar to the one in the sample? Can you check the actual datalength of the value you are trying to return in your query? Using above example, you can do "select datalength(c1) from ##texttab" to verify the length. The query functionality supports text/ntext data so you should be able to create email message >8000 characters.sql

Eliminating Duplicate Records

Hi

How can i eliminate duplicate records from a single table, when i use a query that links multipla tables together?

There is a surrogate key TABLE1 and i just can't seem to wrap my head around the layout.

TABLE1

ID | memQuestionaireID | QuestscoreID | DateOfAssessment | etc.

TABLE2
memQuestionaireID | MemberID | Answer1 | Answer2 | etc.

TABLE3

MemberID | Name | Surname | IDNo | etc.

t1.memQuestionaireID = t2.memQuestionaireID

t2.MemberID = t3.MemberID

That's how the tables link, obvious enough. How can i delete duplicate records from TABLE1 where MemberID occurs more than once and the t1.DateOfAssessment = MAX(DateOfAssessment)

The ID is unique, but there are duplicate memberID's with different date of assessments, i want to take the last used date of assessment and keep only that one, and remove the rest from the table?

I'm sure the query is simple, i'm just too stupid to see how to solve it!!

Any help would be greatly appreciated.

I'm not very clued up on how to remove records or update records, Please help.

Kind Regards
Carel Greaves

See if this does what you need...

Code Snippet

DELETE t1

FROM Table1 t1

INNER JOIN Table2

on t1.memQuestionaireID = t2.memQuestionaireID

INNER JOIN

(

SELECT t2b.MemberID, max(t1b.DateOfAssessment) as DateOfAssessment

FROM table1 t1b

INNER JOIN table2 t2b

ON t1b.memQuestionaireID = t2b.memQuestionaireID

AND t2b.MemberID in

(

SELECT MemberID

FROM Table3

GROUP BY MemberID

HAVING count(*)>1

)

GROUP BY t2b.MemberID

) as list

ON t1.DateOfAssessment < list.DateOfAssessment

and t2.MemberID = list.MemberID

To view what will be delete w/o actually deleting, just change the first line from DELETE t1 to SELECT t1.*

|||

I'm a bit confused Carel,

Call me dense today, but it seems like the MemberID 'should' be in Table1 since it is the Member that has a memQuestionaireID, has a QuestScoreID and a DateOfAssessment. Then for Table2, it is the Questionaire that has a set of answers, etc. Please help me out here...

It seems like your Table1 has a one-to-many relationship with Table2. I'm assuming that a Member may have multiple instances of the same questionnaire (re-takes, etc.)

Is one questionnaire (MemQuestionnaireID) with a QuestScoreID and a DateOfAssessment directly linked to one or more set(s) of answers in Table2?

Is the QuestScoreID changed upon each re-take? (Shouldn't the score be directly related to a set of answers?)

I ask this because it seems from the design you presented that perhaps you want to also delete rows from Table2...

|||

I know the database structure is a bit stupid. Not my design!

I am only trying to remove duplicate entries in the QuestScore table.

Problem:

A member fills in a Questionaire - MemQuestionaires

The Querstionaire's Score is placed in the QuestScore Table.

There are duplicate entries in the QuestScore table with regards to a questionaireID from the memQuestionaires Table

If a duplicate exists, then the members can't log into there profiles anymore, so i need to remove the duplicates so that the members can access their profiles again.

I'm quite new to all of this, so any help would be greatly appreciated

Kind Regards

Carel Greaves.

|||

You gave us Table1, Table2, Table3...I'm guessing Table1 is QuestScore?

Did my code above work? If not, what was not quite right?

Are there multiple entries just in Table1, or is it Table3?

Please clarify and maybe post some sample data...

|||

Okay, so i'm a bito f a moron :-) he he

Here is the code i used to find the duplicates

SELECT me.MemberID, /*me.QuestionaireID,*/ q.memQuestionaireID, q.*, me.*

FROM questscore q, memQuestionaires m, Members me

WHERE q.memquestionaireID = m.memquestionaireID

AND m.MemberiD = me.memberid

AND me.ClientID = 124

ANd m.questionaireid = 85

AND q.sectionid >= 1000000

ORDER BY me.memberID

Here is my results:

MemberID memQuestionaireID QuestScoreID AnswerID AnswerValue DateCompleted 10005772 7615 273308 0 -500 50:59.7 10005789 11124 267771 0 -500 25:16.0 10005798 11129 259150 0 -500 44:41.1 10005798 11129 261032 0 -500 01:21.3 10005807 8214 237464 0 -500 59:18.1 10005819 11142 293317 0 -500 43:09.4

i have marked the duplicate values in red that i want to eliminate.

I need to eliminate only the record with the oldest date, and keep the newer one.

Hopefully when i've gone for my SQL course then i will only be half a moron :-) he he

Thanks for helping me out guys.

Kind Regards

Carel Greaves

|||

Wow, talk about a 'bolixed' up database...

I'm still confused, in your query above, you have a WHERE clause that indicates the results should be filtered down to QuestionaireID = 85, but your duplicate records are for memQuestionaireID 11129. What is the difference between QuestionaireID and memQuestionaireID? How did that happen?

Also, the DateCompleted is obviously not a date, but a number of hours. Is that the number of hours since some event? How are the hours determined?

Which row is the 'newer one'?

It would be very helpful, and keep us from wasting our time, if you would post the DDL for each of the tables, as well as some sample data for each table (in the form of INSERT statements.)

As you seen, you have folks sincerely attempting to help you, and to this point wasting our time because we just don't have the 'full' picture. You've inherited a very 'odd' database, and you need to help us so we can better help you.

|||

Here is sample data in the Tables.

Member Table

med_aid MemberID ClientID Name Surname Username Password NULL 10000000 1 NiftyName NiftySurname his test NULL 10000001 5 NiftyTest2 Surname2 nifty@.ybo.oa JBCYWO NULL 10000002 5 KeithTest WilsonTest willi willi

memQuestionaires Table

memQuestionaireID MemberID QuestionaireID LastPage Paused Complete MaxPages Sent LastSaved InitMail ReceiveMail UpdateDate 871 10000000 85 1 0 0 1 1 0 1 1 2007/02/2506:23 872 10000001 85 1 0 0 1 1 0 1 1 2007/02/2506:23 873 10000002 85 1 0 0 1 1 0 1 1 2007/02/2506:23

QuestScore Table

QuestScoreID memQuestionaireID AnswerID AnswerValue SectionID Page QuestType AnswerText AnswerShort Updatedate DefaultValue DateCompleted 4641 871 0 -9 361 1 9 NULL NULL

2007/02/0722:31

4642 872 0 -5 362 1 6 NULL NULL

2007/02/0722:31

4643 873 0 0 345 1 2 NULL NULL

2007/02/0722:31

When i executed this statement, i only used these three tables.

SELECT me.MemberID, /*me.QuestionaireID,*/ q.memQuestionaireID, q.*, me.*
FROM questscore q, memQuestionaires m, Members me
WHERE q.memquestionaireID = m.memquestionaireID
AND m.MemberiD = me.memberid
AND me.ClientID = 124
ANd m.questionaireid = 85
AND q.sectionid >= 1000000
ORDER BY me.memberID

I am only trying to remove the duplicate records for where ClientID = 124

AND QuestionaireID = 85

as for the 1000000, there it is just to narrow down the search for myself, there are a lot of duplicates.

There are a lot of members -> Members Table

Each member can fill in a number of questionaires -> QuestionaireID on memQuestionaires Table in this case 85

And all the questionaire's data is stored in the QuestScore Table which is linked to the memQuestionaires table using the memQuestionairesID.

There seems to be duplicate values in the QuestScore Table referring to the members.

The date completed is an actual DATETIME field in the database, i used excel to transfer the data to here. The newer date will be a actual date with the time in the database i.e. 2007/05/23 02:05:11

All the fields get inserted into the database via a application which i haven't seen.

I'm new to the company and am trying to figure a lot of it out for myself too.

If there is any more information you need, please don't hesitate to ask.

|||try this one,

DECLARE @.QuestScore TABLE (
QuestScoreID int
, memQuestionaireID int
, DateCompleted smalldatetime
)

DECLARE @.MemQuestionaire TABLE (
MemQuestionaireID int
, MemberID int
, QuestionaireID int
, UpdateDate smalldatetime
)

INSERT
INTO @.QuestScore
SELECT 1, 1, dateadd(day,-1, getdate()) UNION ALL
SELECT 2, 2, dateadd(day,-1, getdate()) UNION ALL
SELECT 3, 3, dateadd(day,-3, getdate())

INSERT
INTO @.MemQuestionaire
SELECT 1, 1, 4, dateadd(day,-1, getdate()) UNION ALL
SELECT 2, 1, 4, dateadd(day,-1, getdate()) UNION ALL
SELECT 3, 2, 5, dateadd(day,-3, getdate())

select *
from @.memquestionaire

select *
from @.questscore

declare @.tobedeleted table( -- create a table var for the data to be deleted
memquestionaireid int
, datecompleted smalldatetime
)

insert
into @.tobedeleted -- the memquestionaireid and datecompleted to be deleted
select MIN(qq.memquestionaireid) as memquestionaireid -- just in case if they have the same date, get the lowest id
, mq.datecompleted
from @.questscore qq inner join
(
select m.memberid
, m.questionaireid
, MIN(q.datecompleted) as datecompleted -- delete the lowest date
from @.memquestionaire m inner join
@.questscore q on m.memquestionaireid = q.memquestionaireid
group by
m.memberid
, m.questionaireid
having count(m.memberid) > 1 -- member having more than 1 quest score
) mq on qq.datecompleted = mq.datecompleted
group by
mq.datecompleted

delete @.questscore -- delete in questscore
from @.questscore q inner join
@.tobedeleted dq on q.memquestionaireid = dq.memquestionaireid
and q.datecompleted = dq.datecompleted

delete @.memquestionaire -- delete in memquestionaire
from @.memquestionaire m inner join
@.tobedeleted dq on m.memquestionaireid = dq.memquestionaireid

select *
from @.questscore

select *
from @.memquestionaire|||

Thanks, that's awesome, that's why i come to you guys for help.

Another quick question, that i think it might be easier to do, i just can't do it. (I just thought of it now)

If i added memQuestionaireID and SectionID together to create a unique field, and then filter out the duplicates by taking out the old dates within the duplicates it might be easier, but how would i go about accomplishing this?

I don't know how to compare the dates against itself to get keep the new date and get rid of the old date?

sorry for all of this i'm just trying to learn from all the things myself?

|||

Thanks, that was a start.

Here is an example of the 'best' form for DDL and sample data. In this fashion, each person that wants to try and help you can just run this code and have 'your' problem to work with. Without this, folks are turned away because it takes so much time and effort to duplicate your situation -and helps to keep us from running off on tangents that don't really help you.

Now if you would only add to the sample data so that examples of the duplicate records you want to find and delete are represented, we 'should' be able to help you.

Code Snippet

DECLARE @.Members table
( med_aid int,
MemberID int,
ClientID int,
Name varchar(20),
Surname varchar(20),
Username varchar(100),
Password varchar(20)
)

INSERT INTO @.Members VALUES ( NULL, 10000000, 1, 'NiftyName', 'NiftySurname', 'his', 'test' )
INSERT INTO @.Members VALUES ( NULL, 10000001, 5, 'NiftyTest2', 'Surname2', 'nifty@.ybo.oa', 'JBCYWO' )
INSERT INTO @.Members VALUES ( NULL, 10000002, 5, 'KeithTest', 'WilsonTest', 'willi', 'willi' )

DECLARE @.memQuestionaires table
( memQuestionaireID int,
MemberID int,
QuestionaireID int,
LastPage int,
Paused int,
Complete int,
MaxPages int,
Sent int,
LastSaved int,
InitMail int,
ReceiveMail int,
UpdateDate datetime
)

INSERT INTO @.memQuestionaires VALUES ( 871, 10000000, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )
INSERT INTO @.memQuestionaires VALUES ( 872, 10000001, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )
INSERT INTO @.memQuestionaires VALUES ( 873, 10000002, 85, 1, 0, 0, 1, 1, 0, 1, 1, '2007/02/25 06:23' )

DECLARE @.QuestScore table
( QuestScoreID int,
memQuestionaireID int,
AnswerID int,
AnswerValue int,
SectionID int,
Page int,
QuestType int,
AnswerText int,
AnswerShort int,
Updatedate int,
DefaultValue int,
DateCompleted datetime
)

INSERT INTO @.QuestScore VALUES ( 4641, 871, 0, -9, 361, 1, 9, '', '', NULL, NULL, '2007/02/07 22:31' )
INSERT INTO @.QuestScore VALUES ( 4642, 872, 0, -5, 362, 1, 6, '', '', NULL, NULL, '2007/02/07 22:31' )
INSERT INTO @.QuestScore VALUES ( 4643, 873, 0, 0, 345, 1, 2, '', '', NULL, NULL, '2007/02/07 22:31' )

|||very clean example arnie.. i wonder how your desk looks like |||

Sorry i'm sending you guys on a wild goose chase like this, and thanks a lot Arnie, i'd buy you a case of beer if i knew where you were.

I'm really learning a lot from this!

I just thought that it might be easier, if i had to add the memQuestionaireID and SectionID together in the QuestScore table to get a single unique value and remove the duplicates based on the newer unique value according to the Date, i.e. Only removing the old dates.

Sorry about this, i only saw it now.

That way i'm only using a single table.

|||

Thanks,

And my desk is under the stuff somewhere, or so it was a few months ago...

And UPS serves Portland, OR! But it would be much more enjoyable to share, so if you can wrangle it, start lobbying management to let you come to the PASS (Professional Association for SQL Server) Conference in September. (http://sqlpass.org/)

|||

Cummon guys, i'm a newbie at SQL Server in South Africa and it's late at night and my CEO's say i can't go home until i get this problem sorted out.

Please could you guys help me out quick?

efficency

i have 3 tables that are linked together and i would like to run a search based on criteria in each table.
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...

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

|||thank you this will help out alot|||alright here is an sql statement i am using

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 C
PrimayTable
 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?