distinct name and email.

hey guys

.

you see how fenix.sn has a couple of entries BUT the same email and DIFFErENT BET IDS.

okay this is what i wanna do.

i wanna send an email to fenix.sn (just ONE email) saying you have won the following bets: xxx, xxx, xxx, xxx

then i wanna delete the entreis from the table.

masfenix,

There are several ways to accomplish this. Let me give you one of them here.

SELECT
[Username],
[Useremail],
WonBetIDs = REPLACE(
(
SELECT
WonBetID AS [data()]
FROM
tblUser tIn
WHERE
tIn.[Username] = tOut.[Username]
ORDER BY
WonBetID
FOR XML PATH ('')
), ' ', ',')
FROM
tblUser tOut
GROUP BY
[Username], [Useremail]

Hope this helps.|||

thanks for that ,but i am not gonna lie. i do not understand htat code at all. i am looking for a function.

|||It's good to be frank. Here is another implementation that may help you to understand easily. Since there isn't any function that does what you had mentioned, I have UDF to do the same for you here. Hope this helps.

Function
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION dbo.getWonBetIdsOfUser
(
@.Username VARCHAR(20)
)
RETURNS VARCHAR(2048)
AS
BEGIN
DECLARE @.WonBetIds VARCHAR(2048)

SELECT
@.WonBetIds = COALESCE(@.WonBetIds + ',', '') + [WonBetID]
FROM
tblUser
WHERE
[Username] = @.Username

RETURN @.WonBetIds

END
GO

Calling function
SELECT [Username], dbo.getWonBetIdsOfUser([Username])
FROM tblUser
GROUP BY [Username]|||

how do i actually call that stored proceudre?

i dont understand the "calling function".

note: i just wanna make a button called "send email" and it should automatically go through the list and send the emails. (but once for each user)

|||Use the query specified under Calling Function part in SqlCommand in your .NET app to get a dataset/datatable containing all username/email and bet ids. You can loop thru each records in the table and mail them from within your .NET application.

displaying non-duplicate keys of all duplicate entries

Ok, so I'm checking for duplicate data in a table. Let's say it has 3 data fields and a key field (i.e. "ID", "FIRST", "MIDDLE", "LAST"). No keys are duplicated. If I find entries that have the same data in each of the non-key fields, I want to know the keys for all those entries. I have been able to find duplicate rows using this...

SELECT
TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
FROM
TABLE
GROUP BY
TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
HAVING
COUNT(*) > 1

Unfortunately I've found no way to incorporate the return of the TABLE."ID" for every duplicated entry. Is there some way I can join the result with the db.table to find this, or some other way to make this happen?

Thanks,
DeanSure, use:SELECT
A."ID", A."FIRST", A."MIDDLE", A."LAST"
FROM TABLE AS A
WHERE 1 < (SELECT Count(*)
FROM TABLE AS B
WHERE B."FIRST" = A."FIRST"
AND B."LAST" = A."LAST"
AND B."MIDDLE" = A."MIDDLE")-PatP|||Thanks, that worked well, although it takes a good while for the server to process the query.|||Indicies would help this query a lot, particularly an index on last, first, middle.

-PatP