Two tables Loop question

Discussion in 'ASP General' started by JP SIngh, May 23, 2005.

  1. JP SIngh

    JP SIngh Guest

    Hi All

    I run a query which gets data from two tables.

    I store in MainContracts Table

    ContractId
    InitiatedBy


    In the ContractDependents table I store

    ContractId
    EmpName

    In the empprofile table I store

    EmpName
    FirstName
    LastName
    Email

    For each contract we have multiple contract dependents.

    What I want to do is to write a query to pull the email address of the
    initator of the record and the email of all the contract dependents to allow
    me to send an email to all of them.

    At present I doing this.(just the logic not the actual code)

    Get contract initiators email

    SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
    EMPProfile.email
    FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
    EMPProfile.EmpName
    WHERE (((Contracts.Id)=54207));

    Send email to the contract initiator

    Get the list of all contract dependents and thier email addresses

    SELECT Contractdependents.contractnumber, Contractdependents.EMPName,
    EMPProfile.EmpName, EMPProfile.email
    FROM Contractdependents LEFT JOIN EMPProfile ON Contractdependents.EMPName =
    EMPProfile.EmpName
    WHERE (((Contractdependents.contractnumber)=54207));

    Loop through the above recordset and send email to each dependent.

    I feel opening two recordsets are slow and not efficient.

    Am I correct? is there a better way of doing this.

    Regards
    Jas
     
    JP SIngh, May 23, 2005
    #1
    1. Advertising

  2. JP SIngh

    MyndPhlyp Guest

    "JP SIngh" <> wrote in message
    news:%23cH9%...
    > Hi All
    >
    > I run a query which gets data from two tables.
    >
    > I store in MainContracts Table
    >
    > ContractId
    > InitiatedBy
    >
    >
    > In the ContractDependents table I store
    >
    > ContractId
    > EmpName
    >
    > In the empprofile table I store
    >
    > EmpName
    > FirstName
    > LastName
    > Email
    >
    > For each contract we have multiple contract dependents.
    >
    > What I want to do is to write a query to pull the email address of the
    > initator of the record and the email of all the contract dependents to

    allow
    > me to send an email to all of them.
    >
    > At present I doing this.(just the logic not the actual code)
    >
    > Get contract initiators email
    >
    > SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
    > EMPProfile.email
    > FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
    > EMPProfile.EmpName
    > WHERE (((Contracts.Id)=54207));
    >
    > Send email to the contract initiator
    >
    > Get the list of all contract dependents and thier email addresses
    >
    > SELECT Contractdependents.contractnumber, Contractdependents.EMPName,
    > EMPProfile.EmpName, EMPProfile.email
    > FROM Contractdependents LEFT JOIN EMPProfile ON Contractdependents.EMPName

    =
    > EMPProfile.EmpName
    > WHERE (((Contractdependents.contractnumber)=54207));
    >
    > Loop through the above recordset and send email to each dependent.
    >
    > I feel opening two recordsets are slow and not efficient.
    >
    > Am I correct? is there a better way of doing this.


    How 'bout:

    SELECT m.ContractId, m.InitiatedBy, e1.EmpName, e1.Email, c.ContractId,
    c.EmpName, e2.Email
    FROM ((MainContracts AS m INNER JOIN EmpProfile AS e1 ON m.InitiatedBy =
    e1.EmpName)
    INNER JOIN ContractDependents AS c ON m.ContractId = c.ContractId)
    INNER JOIN EmpProfile AS e2 ON c.EmpName = e2.EmpName
    WHERE m.ContractId=54207;

    Returns one set of data with the initiator's information repeated in each
    row and the database does all the work. If there is a possibility of
    unmatched records but you still want rows returned use LEFT JOIN and/or
    RIGHT JOIN in place of INNER JOIN where appropriate.
     
    MyndPhlyp, May 23, 2005
    #2
    1. Advertising

  3. JP SIngh

    JP SIngh Guest

    But then how will you write the asp code not to repeat sending the email to
    the initator repeatedly but only once




    "MyndPhlyp" <> wrote in message
    news:...
    >
    > "JP SIngh" <> wrote in message
    > news:%23cH9%...
    > > Hi All
    > >
    > > I run a query which gets data from two tables.
    > >
    > > I store in MainContracts Table
    > >
    > > ContractId
    > > InitiatedBy
    > >
    > >
    > > In the ContractDependents table I store
    > >
    > > ContractId
    > > EmpName
    > >
    > > In the empprofile table I store
    > >
    > > EmpName
    > > FirstName
    > > LastName
    > > Email
    > >
    > > For each contract we have multiple contract dependents.
    > >
    > > What I want to do is to write a query to pull the email address of the
    > > initator of the record and the email of all the contract dependents to

    > allow
    > > me to send an email to all of them.
    > >
    > > At present I doing this.(just the logic not the actual code)
    > >
    > > Get contract initiators email
    > >
    > > SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
    > > EMPProfile.email
    > > FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
    > > EMPProfile.EmpName
    > > WHERE (((Contracts.Id)=54207));
    > >
    > > Send email to the contract initiator
    > >
    > > Get the list of all contract dependents and thier email addresses
    > >
    > > SELECT Contractdependents.contractnumber, Contractdependents.EMPName,
    > > EMPProfile.EmpName, EMPProfile.email
    > > FROM Contractdependents LEFT JOIN EMPProfile ON

    Contractdependents.EMPName
    > =
    > > EMPProfile.EmpName
    > > WHERE (((Contractdependents.contractnumber)=54207));
    > >
    > > Loop through the above recordset and send email to each dependent.
    > >
    > > I feel opening two recordsets are slow and not efficient.
    > >
    > > Am I correct? is there a better way of doing this.

    >
    > How 'bout:
    >
    > SELECT m.ContractId, m.InitiatedBy, e1.EmpName, e1.Email, c.ContractId,
    > c.EmpName, e2.Email
    > FROM ((MainContracts AS m INNER JOIN EmpProfile AS e1 ON m.InitiatedBy =
    > e1.EmpName)
    > INNER JOIN ContractDependents AS c ON m.ContractId = c.ContractId)
    > INNER JOIN EmpProfile AS e2 ON c.EmpName = e2.EmpName
    > WHERE m.ContractId=54207;
    >
    > Returns one set of data with the initiator's information repeated in each
    > row and the database does all the work. If there is a possibility of
    > unmatched records but you still want rows returned use LEFT JOIN and/or
    > RIGHT JOIN in place of INNER JOIN where appropriate.
    >
    >
     
    JP SIngh, May 23, 2005
    #3
  4. JP SIngh wrote:
    > Hi All
    >
    > I run a query which gets data from two tables.


    What database? Type and version, please.

    >
    > I store in MainContracts Table
    >
    > ContractId
    > InitiatedBy
    >
    >
    > In the ContractDependents table I store
    >
    > ContractId
    > EmpName
    >
    > In the empprofile table I store
    >
    > EmpName
    > FirstName
    > LastName
    > Email
    >
    > For each contract we have multiple contract dependents.
    >
    > What I want to do is to write a query to pull the email address of the
    > initator of the record and the email of all the contract dependents
    > to allow me to send an email to all of them.
    >
    > At present I doing this.(just the logic not the actual code)
    >
    > Get contract initiators email
    >
    > SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
    > EMPProfile.email
    > FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
    > EMPProfile.EmpName
    > WHERE (((Contracts.Id)=54207));



    Hmm, all the parentheses makes this look like the sql created by the Access
    Query Builder, so I will assume you are using Access. Please don't make us
    guess. :)

    >
    > Send email to the contract initiator
    >
    > Get the list of all contract dependents and thier email addresses
    >
    > SELECT Contractdependents.contractnumber, Contractdependents.EMPName,
    > EMPProfile.EmpName, EMPProfile.email
    > FROM Contractdependents LEFT JOIN EMPProfile ON
    > Contractdependents.EMPName = EMPProfile.EmpName
    > WHERE (((Contractdependents.contractnumber)=54207));
    >
    > Loop through the above recordset and send email to each dependent.
    >
    > I feel opening two recordsets are slow and not efficient.
    >
    > Am I correct? is there a better way of doing this.
    >
    > Regards
    > Jas



    It sounds like you need a union query. It could be as simple as this:

    SELECT c.Id, c.initiatedby, e.EmpName,e.email
    FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
    e.EmpName
    WHERE c.Id=54207
    UNION
    SELECT c.contractnumber, c.EMPName,e.EmpName, e.email
    FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
    e.EmpName
    WHERE c.contractnumber=54207


    Is the email to the initiator different from the one sent to the dependants?
    If so, you will need to include

    SELECT 'Initiator' As Source,
    c.Id, c.initiatedby, e.EmpName,e.email
    FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
    e.EmpName
    WHERE c.Id=54207
    UNION
    SELECT 'Dependant' As Source,
    c.contractnumber, c.EMPName,e.EmpName, e.email
    FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
    e.EmpName
    WHERE c.contractnumber=54207
    ORDER BY Source DESC


    HTH,
    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], May 23, 2005
    #4
  5. JP SIngh

    JP SIngh Guest

    Thanks bob

    just a follow up on your suggestion

    who would I write the look such that the initiator only gets email once and
    not everytime.

    I mean if there are 6 contract dependents i don't want initator to be
    emailed 6 times but only once


    "Bob Barrows [MVP]" <> wrote in message
    news:u$...
    > JP SIngh wrote:
    > > Hi All
    > >
    > > I run a query which gets data from two tables.

    >
    > What database? Type and version, please.
    >
    > >
    > > I store in MainContracts Table
    > >
    > > ContractId
    > > InitiatedBy
    > >
    > >
    > > In the ContractDependents table I store
    > >
    > > ContractId
    > > EmpName
    > >
    > > In the empprofile table I store
    > >
    > > EmpName
    > > FirstName
    > > LastName
    > > Email
    > >
    > > For each contract we have multiple contract dependents.
    > >
    > > What I want to do is to write a query to pull the email address of the
    > > initator of the record and the email of all the contract dependents
    > > to allow me to send an email to all of them.
    > >
    > > At present I doing this.(just the logic not the actual code)
    > >
    > > Get contract initiators email
    > >
    > > SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
    > > EMPProfile.email
    > > FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
    > > EMPProfile.EmpName
    > > WHERE (((Contracts.Id)=54207));

    >
    >
    > Hmm, all the parentheses makes this look like the sql created by the

    Access
    > Query Builder, so I will assume you are using Access. Please don't make us
    > guess. :)
    >
    > >
    > > Send email to the contract initiator
    > >
    > > Get the list of all contract dependents and thier email addresses
    > >
    > > SELECT Contractdependents.contractnumber, Contractdependents.EMPName,
    > > EMPProfile.EmpName, EMPProfile.email
    > > FROM Contractdependents LEFT JOIN EMPProfile ON
    > > Contractdependents.EMPName = EMPProfile.EmpName
    > > WHERE (((Contractdependents.contractnumber)=54207));
    > >
    > > Loop through the above recordset and send email to each dependent.
    > >
    > > I feel opening two recordsets are slow and not efficient.
    > >
    > > Am I correct? is there a better way of doing this.
    > >
    > > Regards
    > > Jas

    >
    >
    > It sounds like you need a union query. It could be as simple as this:
    >
    > SELECT c.Id, c.initiatedby, e.EmpName,e.email
    > FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
    > e.EmpName
    > WHERE c.Id=54207
    > UNION
    > SELECT c.contractnumber, c.EMPName,e.EmpName, e.email
    > FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
    > e.EmpName
    > WHERE c.contractnumber=54207
    >
    >
    > Is the email to the initiator different from the one sent to the

    dependants?
    > If so, you will need to include
    >
    > SELECT 'Initiator' As Source,
    > c.Id, c.initiatedby, e.EmpName,e.email
    > FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
    > e.EmpName
    > WHERE c.Id=54207
    > UNION
    > SELECT 'Dependant' As Source,
    > c.contractnumber, c.EMPName,e.EmpName, e.email
    > FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
    > e.EmpName
    > WHERE c.contractnumber=54207
    > ORDER BY Source DESC
    >
    >
    > HTH,
    > Bob Barrows
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
     
    JP SIngh, May 23, 2005
    #5
  6. Run my query. The initiator should only be returned once.

    JP SIngh wrote:
    > Thanks bob
    >
    > just a follow up on your suggestion
    >
    > who would I write the look such that the initiator only gets email
    > once and not everytime.
    >
    > I mean if there are 6 contract dependents i don't want initator to be
    > emailed 6 times but only once
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:u$...
    >> JP SIngh wrote:
    >>> Hi All
    >>>
    >>> I run a query which gets data from two tables.

    >>
    >> What database? Type and version, please.
    >>
    >>>
    >>> I store in MainContracts Table
    >>>
    >>> ContractId
    >>> InitiatedBy
    >>>
    >>>
    >>> In the ContractDependents table I store
    >>>
    >>> ContractId
    >>> EmpName
    >>>
    >>> In the empprofile table I store
    >>>
    >>> EmpName
    >>> FirstName
    >>> LastName
    >>> Email
    >>>
    >>> For each contract we have multiple contract dependents.
    >>>
    >>> What I want to do is to write a query to pull the email address of
    >>> the initator of the record and the email of all the contract
    >>> dependents to allow me to send an email to all of them.
    >>>
    >>> At present I doing this.(just the logic not the actual code)
    >>>
    >>> Get contract initiators email
    >>>
    >>> SELECT Contracts.Id, Contracts.initiatedby, EMPProfile.EmpName,
    >>> EMPProfile.email
    >>> FROM Contracts LEFT JOIN EMPProfile ON Contracts.initiatedby =
    >>> EMPProfile.EmpName
    >>> WHERE (((Contracts.Id)=54207));

    >>
    >>
    >> Hmm, all the parentheses makes this look like the sql created by the
    >> Access Query Builder, so I will assume you are using Access. Please
    >> don't make us guess. :)
    >>
    >>>
    >>> Send email to the contract initiator
    >>>
    >>> Get the list of all contract dependents and thier email addresses
    >>>
    >>> SELECT Contractdependents.contractnumber,
    >>> Contractdependents.EMPName, EMPProfile.EmpName, EMPProfile.email
    >>> FROM Contractdependents LEFT JOIN EMPProfile ON
    >>> Contractdependents.EMPName = EMPProfile.EmpName
    >>> WHERE (((Contractdependents.contractnumber)=54207));
    >>>
    >>> Loop through the above recordset and send email to each dependent.
    >>>
    >>> I feel opening two recordsets are slow and not efficient.
    >>>
    >>> Am I correct? is there a better way of doing this.
    >>>
    >>> Regards
    >>> Jas

    >>
    >>
    >> It sounds like you need a union query. It could be as simple as this:
    >>
    >> SELECT c.Id, c.initiatedby, e.EmpName,e.email
    >> FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
    >> e.EmpName
    >> WHERE c.Id=54207
    >> UNION
    >> SELECT c.contractnumber, c.EMPName,e.EmpName, e.email
    >> FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
    >> e.EmpName
    >> WHERE c.contractnumber=54207
    >>
    >>
    >> Is the email to the initiator different from the one sent to the
    >> dependants? If so, you will need to include
    >>
    >> SELECT 'Initiator' As Source,
    >> c.Id, c.initiatedby, e.EmpName,e.email
    >> FROM Contracts c LEFT JOIN EMPProfile e ON c.initiatedby =
    >> e.EmpName
    >> WHERE c.Id=54207
    >> UNION
    >> SELECT 'Dependant' As Source,
    >> c.contractnumber, c.EMPName,e.EmpName, e.email
    >> FROM Contractdependents c LEFT JOIN EMPProfile e ON c.EMPName =
    >> e.EmpName
    >> WHERE c.contractnumber=54207
    >> ORDER BY Source DESC
    >>
    >>
    >> HTH,
    >> Bob Barrows
    >> --
    >> Microsoft MVP - ASP/ASP.NET
    >> Please reply to the newsgroup. This email account is my spam trap so
    >> I don't check it very often. If you must reply off-line, then remove
    >> the "NO SPAM"


    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], May 23, 2005
    #6
  7. JP SIngh

    MyndPhlyp Guest

    "JP SIngh" <> wrote in message
    news:...
    > But then how will you write the asp code not to repeat sending the email

    to
    > the initator repeatedly but only once


    A test to see if the initiator's id changed would do the trick ...


    strInitiator = ""
    rs.Open

    Do Until rs.EOF
    If rs.Fields("InitiatedBy").Value <> strInitiator Then
    ' Do one time stuff here
    strInitiator = rs.Fields("InitiatedBy").Value
    End If
    ' Do repetitive stuff here
    rs.MoveNext
    Loop

    rs.Close
     
    MyndPhlyp, May 23, 2005
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Peter Bassett
    Replies:
    3
    Views:
    947
    Augustus
    Aug 15, 2003
  2. Otuatail

    Tables within tables

    Otuatail, Jul 31, 2004, in forum: HTML
    Replies:
    7
    Views:
    510
  3. GenxLogic
    Replies:
    3
    Views:
    1,329
    andrewmcdonagh
    Dec 6, 2006
  4. Paolo

    Two dropdownlist and two tables...

    Paolo, Sep 19, 2006, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    148
    Paolo
    Sep 19, 2006
  5. Isaac Won
    Replies:
    9
    Views:
    398
    Ulrich Eckhardt
    Mar 4, 2013
Loading...

Share This Page