12,885,742 members (42,498 online)
Rate this:
See more:
I have two tables:
tblCust(id, name, phone)
Data:
id| name| phone
1 | abc | 99565665
2 | def | 98654571
3 | ghi | 96554574

and
tblOrder(OrderNo,id,oSDate,oEndDate)
Data:
OrderNo | id | oSDate | oEndDate
o1 | 1 | jan1,2012 | mar1,2012
o2 | 1 | jan2,2012 | mar3,2012
o3 | 2 | feb1,2012 | apr3,2012
o3 | 2 | feb2,2012 | apr4,2012
o3 | 2 | feb3,2012 | apr4,2012
o3 | 2 | feb4,2012 | apr3,2012

Now, i want to get the latest orderDetails along with customerDetails for each customer
Posted 4-Jan-13 19:47pm
pryashrma1.1K
Updated 4-Jan-13 20:43pm
v3
pryashrma 5-Jan-13 2:28am

plz refer updated question

Rate this:

## Solution 1

Please refer the below links then you get some information regarding this

http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/[^]

http://www.aspdotnet-suresh.com/2011/12/different-types-of-joins-in-sql-server.html[^]

http://beginner-sql-tutorial.com/sql-joins.htm[^]

http://www.techonthenet.com/sql/joins.php[^]

pryashrma 5-Jan-13 1:59am

Thanks for trying
Rate this:

## Solution 2

Below query will work..
```;WITH cte as
(
SELECT id,max(oSDate) AS latestStartDate
FROM tblOrder
group BY id
)

SELECT C.*,O.*
FROM tblCust AS C
LEFT JOIN  tblOrder  AS O on  O.id =C.id
LEFT JOIN cte AS Temp on Temp.id = C.id
WHERE O.oSDate = Temp.latestStartDate```
v2
pryashrma 5-Jan-13 1:58am

Thanks alot
pryashrma 5-Jan-13 2:12am

not working... could u plz explain the functionin of top 1
chinna punnam 5-Jan-13 6:32am

Actually,I though that you want latest record irrespective of each customer.Thats why i used TOP(1),Which retrieves only one topest record.Try the following query for your requirement..
;WITH cte as
(
SELECT id,max(oSDate) AS latestStartDate
FROM tblOrder
group BY id
)

SELECT C.*,O.*
FROM tblCust AS C
LEFT JOIN tblOrder AS O on O.id =C.id
LEFT JOIN cte AS Temp on Temp.id = C.id
WHERE O.oSDate = Temp.latestStartDate

Top Experts
Last 24hrsThis month
 OriginalGriff 335 ppolymorphe 185 CHill60 155 Jochen Arndt 145 Maciej Los 140
 OriginalGriff 4,247 CHill60 2,513 Karthik Bangalore 2,436 Jochen Arndt 2,173 ppolymorphe 1,985