Sending notification email twice??

[AdeleMB]

Hi everyone,

I have an SQL query that sends an email automatically.
We have just noticed that the code is sending the exact same email out twice and I dont know why? :confused:
(I didnt write the code but have been asked to try to fix it!)
Can anyone see an obvious problem with the following code?


Gtga_df_ThreadManager.prototype.notifyUsers = function()
{
var newConn = new openConnection();

var sql = '!nocache:SELECT Watcher.firstName, Watcher.lastName, Watcher.email, Author.userdisplayName, Threads.subject '
+ ' FROM WatchThreads, GTGAUsers Watcher, GTGAUsers Author, Threads '
+ ' WHERE WatchThreads.userID = Watcher.userID '
+ ' AND Threads.masterID = WatchThreads.masterID '
+ ' AND Author.userID = Threads.userID '
+ ' AND NOT Author.userID = Watcher.userID '
+ ' AND Threads.threadID = ' + this.threadID
+ ' AND WatchThreads.masterID = ' + this.masterID

var result = newConn.execute(sql);

while(result)
{
var mail= new SendMail();
mail.To = result[2] ;
mail.From ="xxx@xxx.com";
var mailStr="";
mailStr+= "<font face='Arial'>Dear " + result[0] + " " + result[1] + ",<br /><br />";
mailStr+= result[3] + " has posted a new message in the '" + result[4] + "' thread.<br /><br />font>";
mail.Body = mailStr;
mail.Subject = "New message posted in the '" + result[4] + "' thread";
mail.send();


result= newConn.nextRow();
}
closeConnection(newConn);
}


I know its probably something really obvious but Im still learning most of this so any help would be fantastic! :D

Thank you in advance!

[legendx]

Print out the sql variable and execute the query in another program.. phpMyAdmin, MySQL query browser, SQL Server Manager, etc etc (whatever will allow you to easily execute a query and see the results).. just to see exactly what the query is returning.

My hunch is that since it appears you are joining tables that for every user in the 'GTGAUsers Watcher' table there are two records in one of the other tables so it will return the same email address twice but a different value for one of the other fields.

If you try my suggestion and you see identical rows returned then use SELECT DISTINCT at the beginning of your query and that should take care of things.

[AdeleMB]

Haha you really are a legend!
Adding DISTINCT fixed it, thanks so much :)