«

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" <> 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.

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 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"

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"
>
>

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.

"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