complex SQL statement (SQL Server)

Results 1 to 2 of 2

Thread: complex SQL statement (SQL Server)

  1. #1
    Join Date
    Dec 1969

    Default complex SQL statement (SQL Server)

    I need to construct a sql statement that draws from multiple tables in a SQL Server database. This is for a script that sends emails to large numbers of customers based on their stated preferences. <BR><BR>I am using a Template (stored in a database) for the message body and subject line. And I am recording which Templates are sent to each customer so that I can make sure that each customer only receives each template once. This recording feature is important in case the script throws an error or the server restarts mid-process, etc. I have a long-enough window to protect against the script timing out, but the administrator is non-technical and there are always countless potential problems that could arise, and that we need to prepare for.<BR><BR>To accomplish this, my 3 SQL statements need to list all unique email addresses (with the specified preference from a web form)<BR><BR>This needs to link the Locations, LocationEmails, and LocationPractices tables. <BR><BR>I am envisioning something like: <BR><BR>"SELECT DISTINCT CenterEmail FROM Locations INNER JOIN LocationPractices ON Locations.CenterID=LocationPractices.CenterID WHERE LocationPractices.DescriptionCode = "&#039; & strDescriptionCode & &#039;"<BR><BR>The problem is that I need to also add a check to make sure that they have not received the template yet. I need to accomplish this by joining the query with the pcLocationEmails table. I can do the JOIN on pcLocationEmails.MailToAddress=Locations.CenterEma il, and I can also check for a match between a VB variable TemplateID and pcLocationEmails.EmailTemplateID <BR><BR>But how do I put all these elements into one single, functioning query? <BR><BR>a summary of the relevant tables in SQL Server is as follows: <BR><BR>pcLocationEmails Table <BR>------------------------ <BR>EmailID <BR>FromAddress <BR>MailToAddress <BR>Subject <BR>DateTimeSent <BR>EmailTemplateID &#039; this is an FK to pcEmailTemplates table <BR><BR><BR>Locations Table <BR>------------------------ <BR>CenterID <BR>CenterName <BR>CenterEmail <BR>CenterURL <BR>&#039; other fields <BR><BR><BR>LocationPractices Table <BR>------------------------ <BR>CenterID <BR>DescriptionCode <BR><BR><BR>pcEmailTemplates Table <BR>------------------------ <BR>EmailTemplateID <BR>Subject <BR>MessageBody

  2. #2
    Join Date
    Dec 1969

    Default Looks like NOT IN is needed

    SELECT CenterEmail <BR>FROM Locations, LocationPractices<BR>WHERE LocationPractices.DescriptionCode = &#039;xxx&#039;<BR> AND Locations.CenterID = LocationPractices.CenterID <BR> AND Location.CenterEmail NOT IN (<BR> SELECT MailToAddress FROM pcLocations<BR> WHERE pcLocations.EmailTemplateID = NNN )<BR><BR>If you need a DISTINCT in that query, some is screwy in your data.<BR><BR>You *really* should have a LocationID in that Locations table and then "join" (or, in this case, un-join?) to that rather than the CenterEMail. The use of an integer ID would be much much more efficient than the use of a VarChar like CenterEMail.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts