Results 1 to 3 of 3
  1. #1
    Join Date
    May 2011
    Posts
    2

    Unanswered: Select first match in child table for each row in parent table

    Hello.

    This is more a general SQL question.

    I'm trying to return just the FIRST match from a child table which is correlated with a related row in a parent table. For instance:

    for table parent with columns (id, name) and these values:

    (1, "Bill")
    (2, "Joe")

    for table child with columns (parent_id, name, birth_ord)

    (1, "Bob", 2)
    (1, "Eve", 1)
    (1, "Dan", 3)
    (2, "Sue", 2)

    Can I create a single sql statement (without using intermediate temp tables) that will return the first child for each parent, like this?

    (id, parent name, child name)
    (1, "Bill", "Eve")
    (2, "Joe", "Sue")

    I was hoping to use "select first 1" within a subquery but evidently this is not allowed.

    Thanks,

    Bill

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT parent.id
         , parent.name AS parent_name
         , child.name AS child_name
      FROM parent
    LEFT OUTER
      JOIN ( SELECT parent_id
                  , MIN(birth_ord) AS firstborn
               FROM child
             GROUP
                 BY parent_id ) AS c
        ON c.parent_id = parent.id
    LEFT OUTER
      JOIN child
        ON child.parent_id = c.parent_id 
       AND child.birth_ord = c.firstborn
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2011
    Posts
    2
    Thanks, that all makes sense.

    That gives me a starting point for my more complicated table setup. (I'm actually going from an id table to reference an addresses table to pull the hightest priority email address which could have start and end dates and where the priority code for all email codes is stored in a 3rd table)

Posting Permissions

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