#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    1
    Rep Power
    0

    Question Joining tables to return top N linked records


    I am joining 2 tables that have a 1-to-Many relationship (for example Authors to Books). I would like there to be only one row returned per Author & Book combo.

    Say,

    SELECT
    A.Name,
    B.PublishedDate,
    B.Title
    FROM
    Authors A
    LEFT JOIN Books B ON A.Author = B.Author

    but I would like to alter this query so there is only row per author, and the book displayed is the most recently published (B.PublishedDate) or some other criteria -- this is just an example.

    I am sure there is an efficient/easy way in SQL to do this, but I can't quite get it.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,382
    Rep Power
    391
    Code:
    SELECT
    A.Name,
    B.PublishedDate,
    B.Title
    FROM
    Authors A
    LEFT JOIN Books B ON A.Author = B.Author
    and publishDate in (select max(publishDate)
    from books where author = b.author)

IMN logo majestic logo threadwatch logo seochat tools logo