Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Two tables Loop question

Reply
Thread Tools

Two tables Loop question

 
 
JP SIngh
Guest
Posts: n/a
 
      05-23-2005
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



 
Reply With Quote
 
 
 
 
MyndPhlyp
Guest
Posts: n/a
 
      05-23-2005

"JP SIngh" <(E-Mail Removed)> wrote in message
news:%23cH9%(E-Mail Removed)...
> 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.


 
Reply With Quote
 
 
 
 
JP SIngh
Guest
Posts: n/a
 
      05-23-2005
But then how will you write the asp code not to repeat sending the email to
the initator repeatedly but only once




"MyndPhlyp" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> "JP SIngh" <(E-Mail Removed)> wrote in message
> news:%23cH9%(E-Mail Removed)...
> > 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.
>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-23-2005
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"


 
Reply With Quote
 
JP SIngh
Guest
Posts: n/a
 
      05-23-2005
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]" <(E-Mail Removed)> wrote in message
news:u$(E-Mail Removed)...
> 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"
>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      05-23-2005
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]" <(E-Mail Removed)> wrote in message
> news:u$(E-Mail Removed)...
>> 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.


 
Reply With Quote
 
MyndPhlyp
Guest
Posts: n/a
 
      05-23-2005

"JP SIngh" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Triple nested loop python (While loop insde of for loop inside ofwhile loop) Isaac Won Python 9 03-04-2013 10:08 AM
Two dropdownlist and two tables... Paolo ASP .Net Web Controls 0 09-19-2006 04:42 PM
How to get database metadata information (i.e. existing tables and columns in tables) Chris Brat Python 5 08-22-2006 05:25 PM
Tables within tables Otuatail HTML 7 08-02-2004 04:42 AM
Text after 2 tables actually appears between 2 tables ! Peter Bassett HTML 3 08-15-2003 06:46 PM



Advertisments