Join Condition problem

Results 1 to 2 of 2

Thread: Join Condition problem

  1. #1
    Join Date
    Dec 1969

    Default Join Condition problem

    I am having two tables A and B. My join condition is based on &#039;ID&#039; column of both the tables. In table B there is a column which will have some date values.<BR>For one &#039;ID&#039; in table A, there will be many rows in table B for that matching &#039;ID&#039;. But I want to get the only one matching &#039;ID&#039; row from table B based on the latestdate.<BR>I tried like following but giving duplicate rows also<BR><BR>Select A.shipmentid, A.loaddate--, Shp.loaddate<BR>from A, B<BR>where A.shipmentid = B.shipmentid<BR>order by B.loaddate desc<BR><BR>Pls. help me out....

  2. #2
    Join Date
    Dec 1969

    Default Not a join problem, per se...

    Just need to specify the query right. It would work the same way if you were *only* looking at table B:<BR><BR>SELECT shipmentID, Max(loaddate) FROM B<BR>GROUP BY shipmentID<BR>ORDER BY shipmentID<BR><BR>Yes? Won&#039;t that give you what you want? <BR><BR>For each shipmentID in B, you get one an only one record: the one corresponding to the latest loaddate.<BR><BR>Except it gives you none of the info from table A, of course.<BR><BR>Okay, so add the join:<BR><BR>SELECT A.*, Max(B.loaddate) <BR>FROM A, B<BR>WHERE A.shipmentID = B.shipmentID<BR>GROUP BY B.shipmentID<BR>ORDER BY B.shipmentID<BR><BR>I think that works, doesn&#039;t it???<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts