Need help in writing sql query.

[Mudassir]

Dear all.
Let me explain a simple scenario.
suppose i have two tables tbl_Leads and tbl_Leads_Corre spondence.
and i have following data in both table

tbl_Leads
LeadID lName lDate
1 first 2014-11-24 22:22:52.523
2 second 2014-11-25 22:22:52.523
3 third 2014-11-26 22:22:52.523
4 fourth 2014-11-27 22:22:52.523
5 fifth 2014-11-28 22:22:52.523
6 sixth 2014-11-29 22:22:52.523



tbl_Leads_Corre spondence
ID LeadID byName Date
1 1 waqas 2014-11-24 22:22:52.523
2 3 waqas 2014-11-26 22:22:52.523
3 4 wilfred 2014-11-27 22:22:52.523
4 5 wilfred 2014-11-28 22:22:52.523
5 6 wilfred 2014-11-29 22:22:52.523
6 6 waqas 2014-11-29 22:22:52.523
7 6 waqas 2014-11-29 22:22:52.523
8 6 waqas 2014-11-29 22:22:52.523
9 6 waqas 2014-11-29 22:22:52.523
10 6 waqas 2014-11-29 22:22:52.523
11 6 waqas 2014-11-29 22:22:52.523
12 1 wilfred 2014-11-29 22:22:52.523

it can be seen that the second table tbl_Leads_Corre spondence has leadid as foreign key from the first table.

now my problem is that
i want only those leadID and total count of leadIDs from the table tbl_Leads_Corre spondence where byName= wilfred
which are initiated by wilfred.
for example,
LeadID 6 in tbl_Leads_Corre spondence has multiple records but i want the leadid 6 only when it was first entered by wilfred. Like the leadID will be returned only in case of wilfred as wilfred was the first person who entered the record in database.
similarly the leadID from tbl_Leads_Corre spondence will only be returned when the byName 'waqas' will be passed to the query as parameter.
i hope i make my self clear..
any help or sample query will be greatly appreciated..

Regards:

[aflores41]

SELECT tbl_Leads_Correspondence.*, tbl_Leads.*
FROM tbl_Leads INNER JOIN tbl_Leads_Correspondence ON tbl_Leads.LeadID = tbl_Leads_Correspondence.LeadID
WHERE (((tbl_Leads_Correspondence.byName)="wilfred"));

[Rabbit]

I don't understand what results you are looking for from that sample data. Please provide the sample results you expect rather than trying to describe it.

[Mudassir]

Dear rabbit
the results i want from tbl_leads_cerre spondence are

Name leadid
wilfred 4
Wilfred 5
Wilfred 6

The record against wilfred also exists with lead id 1. But that was initiated by waqas. And the leadIds 4,5,6 were started by wilfred in the correpondence table.
i hope i make myself clear now..

[Rabbit]

You need a query that returns the minimum date per lead. You then join that back to the table to get the rest of the data and filter for Wilfred.

[Mudassir]

Thanx dear i got the idea. Will try it and hope it will work as per my requirement.