Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Access
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
Edited 4-Jan-13 20:43pm
v3
Comments
pryashrma at 5-Jan-13 2:28am
   
plz refer updated question
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi, i think Your asking something about joins.
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[^]
 
please refer this links definately you got some idea .
  Permalink  
Comments
pryashrma at 5-Jan-13 1:59am
   
Thanks for trying
Rate this: bad
good
Please Sign up or sign in to vote.

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
  Permalink  
v2
Comments
pryashrma at 5-Jan-13 1:58am
   
Thanks alot
pryashrma at 5-Jan-13 2:12am
   
not working... could u plz explain the functionin of top 1
chinna punnam at 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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 6,649
1 Sergey Alexandrovich Kryukov 6,280
2 CPallini 5,230
3 George Jonsson 3,574
4 Gihan Liyanage 2,522


Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 5 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100