Query Help - must be a simple one

[sd110404]

Hello Everyone,

I have two tables almost the same structure
eg: Table1 and Table2 both has the following fields along with couple other field.

Table1/ Table2
--------------
JNo Ono Source ClientId StartDate ......
1 1001 S1 2 01/01/2008 ......
2 1002 S3 2 02/01/2008 ......
3 1003 S11 3 01/02/2005 ......
4 1004 S2 3 01/02/2004 ......
5 1005 S1 3 01/02/2008 ......

I want my query to return the follwoing result.

Output
-------

JNo Ono Source ClientId StartDate
1 1001 S1 2 01/01/2008
4 1004 S2 3 01/02/2004

Its based on the clientID and StartDate, each clients oldest startdate records needs to be retrived .

I am sure it must be a cup of tea, but I am not taht good at SQl.

I would really appreciate for any help.

Thanks.

 

[fneily]

Have you studied normalization? See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

You have two tables with the same fields. Violates normalization protocols. You're showing one set of data. Is the same data in both tables? Again, not too good. Thus your problem.

Does both tables have a JNo 1, etc.? Then first you have to find the min of the JNo's. Then find the min for client/startdate. But it's unclear.

Post both tables structures and some sample data.

Or redesign the database.

 

[lespaul]

while he is correct that you shouldn't have the same data in two locations does it matter which table the information comes from?

SELECT JNo, Ono, Source, B.ClientId, B.OldestDate 
FROM TableName T1
INNER JOIN (SELECT ClientId, Min(StartDate) As OldestDate
FROM TableName
GROUP BY ClientId) B ON T1.ClientID = B.ClientID and T1.StartDate = B.OldestDate

this should get you started...be sure to fix the table name and field names if necessary.

Leslie

Have you met Hardy Heron?

 

[sd110404]

Sorry, I wasnt clear in my previous post, Table1 is from access. Those datas taht I have is in MS Access and Table2 is in my MS SQL.

Please ignore that I have two tables. Just the table1, and those are the sample data.

Let me know If I should paste my original table structure and datas?

Thanks

 

[lespaul]

did you try the SQL?

Leslie

Have you met Hardy Heron?

 

[sd110404]

Thanks Leslie, Tried your query, it works fine.

Thank you.