Results 1 to 3 of 3

Thread: JOIN trouble

  1. #1
    Join Date
    Sep 2003
    Posts
    34

    Unanswered: JOIN trouble

    I'm trying to create an SQL expression that returns the greatest expiry date associated with a single person. The tables look like this:

    Person:
    MemberID
    FirstName
    .
    .
    .
    ContactID

    Contact:
    ContactID
    .
    .
    .

    Trans:
    TransactionID
    ExpiryDate
    .
    .
    .
    MemberID


    strSQL = "SELECT Person.MemberID, Person.Surname, Person.FirstName, Person.DateOfBirth, Contact.FlatHouseNo, Contact.StreetName, Contact.Suburb, Contact.TownCity, Contact.PhoneNo, Trans.ExpiryDate FROM (Person INNER JOIN Contact ON Person.ContactID = Contact.ContactID) LEFT JOIN Trans ON (Person.MemberID = Trans.MemberID AND Trans.ExpiryDate = )"

    So in my VB app I want to return a row for every person, but since a person may have more than one transaction linked with them i only want to return the transaction with the greatest expiry date.

    I have been trying to figure this out for a while..

    Any ideas?

  2. #2
    Join Date
    Mar 2004
    Posts
    13

    Re: JOIN trouble

    Originally posted by raydenl
    I'm trying to create an SQL expression that returns the greatest expiry date associated with a single person. The tables look like this:

    Person:
    MemberID
    FirstName
    .
    .
    .
    ContactID

    Contact:
    ContactID
    .
    .
    .

    Trans:
    TransactionID
    ExpiryDate
    .
    .
    .
    MemberID


    strSQL = "SELECT Person.MemberID, Person.Surname, Person.FirstName, Person.DateOfBirth, Contact.FlatHouseNo, Contact.StreetName, Contact.Suburb, Contact.TownCity, Contact.PhoneNo, Trans.ExpiryDate FROM (Person INNER JOIN Contact ON Person.ContactID = Contact.ContactID) LEFT JOIN Trans ON (Person.MemberID = Trans.MemberID AND Trans.ExpiryDate = )"

    So in my VB app I want to return a row for every person, but since a person may have more than one transaction linked with them i only want to return the transaction with the greatest expiry date.

    I have been trying to figure this out for a while..

    Any ideas?
    hello raydenl, this problem looks v.simlar to one i had, see the thread "simply impossible" in this forum. blindman provided an answer for me which i think you could adapt fairly easily...

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ...LEFT JOIN Trans
    ON Person.MemberID = Trans.MemberID
    AND Trans.ExpiryDate =
    ( select max(ExpiryDate) from Trans
    where MemberID = Person.MemberID )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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