Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    4

    Unanswered: one to many query question

    I have two tables: tblTest and tblTestingDates they are sent up as a one to many, meaning for every one test I can have many testing dates. What I want returned from a query are tests from tblTest that have a test start date or a test stop date in tblTestingDates ordered by the test start date, but I only want to see the test once in the query. For example if Test #1 has two start dates in tblTestingDates, I only want to return the first Test with its Start Date.

    Any Ideas?

    Thanks in Advance
    George Rapko

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is the first test the oldest or newest start date? The physical order of the records in the table means nothing, you'll have to provide some sort of chronological order to return the records in.

    In general:

    SELECT DISTINCT yourTest, MAX(yourDate)
    FROM yourTestTable INNER JOIN yourTestDates ON some_criteria=some_criteria
    GROUP BY yourTest
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Teddy
    In general:

    SELECT DISTINCT yourTest, MAX(yourDate)
    FROM yourTestTable INNER JOIN yourTestDates ON some_criteria=some_criteria
    GROUP BY yourTest
    no, no, no, please, no

    if you have GROUP BY yourTest, then every value of yourTest in the result set will be distinct, so no matter what the max values are, the rows will still all be distinct

    so adding DISTINCT is totally unnecessary and will only reduce your performance

    ("like dental floss at a willie nelson concert")

    in general, never use DISTINCT with GROUP BY

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Good point and agreed.

    I shall hire a shapely gal in a catholic school uniform to slap my knuckles with the business side of a wooden ruler.

    *hangs head in shame*
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Mar 2006
    Posts
    4

    Thanks for the help

    Thanks to both of you. You hit the nail right on the head. By the way, I was talking about the first start date (in date order not location in the table) sorry for leaving that detail ambiguous. Teddy, you can send that Catholic girl my way....

    thanks again,
    George

Posting Permissions

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