Two tables Loop question

J

JP SIngh

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
 
M

MyndPhlyp

JP SIngh said:
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.
 
J

JP SIngh

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

Bob Barrows [MVP]

JP said:
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
 
J

JP SIngh

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
 
M

MyndPhlyp

JP SIngh said:
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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top