Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
benricho
Yak Posting Veteran
84 Posts |
 Posted - 2003-06-29 : 23:14:39
|
| I am building a system to allow people to send out an email to people who have signed up to receive a newsletter from their site. They can have multiple lists of subscriber, and a list can be on more than one subscriber. When the user is sending the email, they pick what lists they want to send it to (one or more). I want to make sure that the user only receives the email once, even if they are on multiple lists. When the user selects the lists they want the email to be sent to, I insert the emailID and the subscriberID into a separate table (EmailSubscribers) which contains all the users the email should be sent to. As each email is sent I want to remove the row from the table corresponding to the subscriber the email was sent to. This method should allow me to resume sending the emails if there is a problem half way through the process (power failure etc).When I populate the EmailSubscribers table, I want to make sure a subscriber is not added if a subscriber with the same email address has already been added. Should I use a cursor to loop through each row and check that a subscriber with the same email address doesn’t already exist in the EmailSubscribers table, or is there a way I can do this without a cursor? |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-06-30 : 02:59:16
|
quote:
When I populate the EmailSubscribers table, I want to make sure a subscriber is not added if a subscriber with the same email address has already been added. Should I use a cursor to loop through each row and check that a subscriber with the same email address doesn’t already exist in the EmailSubscribers table, or is there a way I can do this without a cursor?
Use a constraint here, either primary key or a unique key constraint on the emailID and subscriberID columns. This will prevent adding duplicates to your table. Also, I would recommend not deleting from this EmailSubscribers table. Instead, try updating a status column as you process each email request. That way you can track who has been sent and email and when (if you use a datetime datatype). You can also restart your processing where you left off if it gets interrupted. -ecEdited by - eyechart on 06/30/2003 03:04:06 |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-06-30 : 03:19:26
|
Hey no cursors needed!  Like eyechart suggested you can create a UNIQUE constraint on the email address column. When you attempt to insert a duplicate email address, you will get an error, which you will have to catch, ignore and move on. If error handling is not your thing, you can also check for existance before inserting the record:IF NOT EXISTS (SELECT EmailAddress FROM EmailSubscribers WHERE EmailAddress = @EmailID)OR INSERT INTO EmailSubscribers SELECT SubscriberID, EmailID FROM MailingList WHERE EmailID NOT IN(SELECT EmailAddress FROM EmailSubscribers)You could also insert all the subscribers you want to sent emails to, into the EmailSubscribers table and then delete all the duplicates.Owais |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2003-06-30 : 03:49:54
|
Thanks for the replies but they don’t quite solve my problem, probably because I didn’t explain it properly. eyecharts solution won’t work because I do want to be able to have the email address appear in my list of subscribers, but they could be for different email lists, such as: subscriberID | emailAddress | listID -----------------|---------------------------|----------1 | userA@hotmail.com | 1 2 | userB@hotmail.com | 1 3 | userA@hotmail.com | 2 4 | userC@hotmail.com | 2 And a basic version of my email table could look like: emailID | subject | body -----------------|---------------------------|----------1 | here is the subject… | body text… Lets say I want to sent my email to everyone on list 1 and 2, then my EmailSubscribers table should look like this: emailID | subscriberID ----------|-----------------1 | 11 | 21 | 4 At the moment, I am getting subscriber 3 added to my EmailSubscribers table as well. I tried checking the EmailSubscribers table to make sure a user with the same email address didn’t already exist (using a similar NOT IN query), but it still inserted the duplicates. This was because there was duplicates on the one list, and when I made the insert statement the EmailSubscribers table was empty, so it just inserted everything on the list (This is why I thought I would need a cursor so that I could check the EmailSubscribers table before each row was added).But if I keep my lists clean so that there are no duplicates on a single list, then the NOT IN query should work, so actually I guess you really did both answer my question. If I did allow duplicates on the one list, would there be an easy way to stop them being inserted into my EmailSubscribers table? |
|
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-06-30 : 04:07:54
|
| DISTINCT is your friend...DavidM"SQL-3 is an abomination.." |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2003-06-30 : 19:16:41
|
| Where should I be using DISTINCT byrmol? I am inserting the subscriberID into the EmailSubscribers table, and there could be 2 different subscriberID's that have the same email address (see subscribers 1 and 3 in my table above), so I want to only send it to one of the subscribers, so doing a INSERT INTO ... SELECT DISTINCT would still give me all 4 records, unless there is a way to do a SELECT DISTINCT based on only 1 column and not the entire row returned that I don't know about? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-30 : 20:10:09
|
quote:
unless there is a way to do a SELECT DISTINCT based on only 1 column and not the entire row returned that I don't know about?
OK, What's so important about the identity column? You're worried about the Email Right?Also, the tables you show have no relationship between them at all. You're missing a table that establishes that.With that you can only build a cartesan product.SELECT * FROM EmailTable, (SELECT DISTINCT EMailAddress FROM Subscriber)Should do it, but I don't reccommend it.Also Your problem with Distinct come from the fact the YOU have to make a decision.You've basically given an EmailAdress to different "IDs".Which one do you want to "Win". And if that's the case, it's so arbitray that you could never recreate it (I guess, at least not easily) and will cause you all sorts of audit nightmares (Audit, that's a word).MOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOOI guess I'm saying you need to revisit the data model.I'll tell you this, if it's implemented, tough you have to work through it.If it's not, it's time to revisit.If a matter of understanding, do you have an ER Diagram to look at?What stage are you at?And a Bovine MOO for Mr. Mist.(Wow what a diatribe....)We're all here to help. Hope I wan't too heavy handed (but damn now 8:10pm est)M U S T L E A VE K E Y BOAR D...Brett8-) |
|
|
|
benricho
Yak Posting Veteran
84 Posts |
Posted - 2003-06-30 : 21:11:26
|
| I actually need more than just the emailAddress column when I am sending the email, I use other data such as name and the subscriberID (to create unsubscribe link, check if user has opened email etc), so that’s why I am trying to work the subscriberID instead of just the email address.And yes, my data model isn’t the best, but I have to have the ability for one email address to be on more than one list, and therefore have more than one subscriberID…. Maybe I should move my subscriberID and ListID into a separate table. I didn’t go this way to start with because I figured that since my system would be being used by multiple clients, I didn’t want one client changing the details of a subscriber which could also belong to another client. But since s subscriber is related to a specific list which is related to a specific client this shouldn’t be a problem.But does this really help? I still have to make sure I don’t have duplicate subscribers, and that they don’t subscribe to the same list more than once. When I select all the ID’s to be emailed from the EmailSubscribers table I could use DISTINCT which would eliminate any duplicates. But this would also mean that when I was unsubscribing users I couldn’t just use their ID, I would also have to know the list they wanted to unsubscribe from. So is this a better technique than the one described at the bottom of post #4 (clean lists + NOT IN query)?Also eyechart suggested not deleting rows from the EmailSubscribers table, but updating and flagging them. This was my original way of thinking, but was a little worried that over time the table would get huge an become slow, would this be a problem (there WHERE clause would be on the emailID which is a Foreign Key (which means it has an index on it??))?Thanks for all the input by the way!! |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-01 : 09:13:16
|
| A better data model will make your life way easier...but if you're already implemented then it's a problem.However a one time change, and the pain it would cause, might be beneficial.Your "Subscriber" table Needs to be broken out. You should have 1 table that just holds distinct Emails.These are your "Clients" and should only be represented once. The fact they want to be on multiple mailing lists should be another table the describes this fact.Another table that holds the Emails Mailings (like you have now) is also independent.You need to represent singularity of data.THEN, worry about relating the information.(And even before worrying about singilarity of data (non repeating values) the individual collection of required data elements)This is data modeling 101. A good design makes EVERYTHING easier, just as the converse of that statement is true.For example, I don't see a relationship between a List table and a Mailing table. I assume that a mailing is done on behalf of a list? No? That would be like an "Event" that has to happen. I would then relate that to the subscribers, because that "single" event (the mailing) would then relate to this list of subscribers, again which are represented once. There by eliminating your problem.I believe your model is representing many to many relationships, which is bad (as you've found out).Without altering the model, I guess you could create views to mimic the actual relationships, but you'd have to build this stuff, so why not build the new data model?Sorry, that's the best I can do...Brett8-) |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-07-01 : 09:49:19
|
quote:
unless there is a way to do a SELECT DISTINCT based on only 1 column and not the entire row returned that I don't know about?
just select the columns you wish to be distinct, not all of them.SELECT DISTINCT EmailAddress FROM someTableIf you need other columns, hopefully you can see that you CAN'T logically return other columns unless you summarize them somehow (SUM, MAX, MIN, etc).IN that case, you use GROUP BY:SELECT EmailAddress, MIN(Col2), Max(col3)FROMSometableGROUP BY EmailAddress- JeffEdited by - jsmith8858 on 07/01/2003 09:49:57 |
|
|
|
|
|
|
|
|
Is a cursor the only way I can do this?