CODEWITHSUNDEEP
sqlsql-server
Wally Hartshorn
Wally Hartshorn

Reputation: 915

Display Data from Table with Latest Associated Data from Two Related Tables

I have 3 tables for Authors, Books, and Articles. An Author can have multiple Books and multiple Articles, so those have AuthorId as a foreign key.

Authors

Books

Articles

For each Author, I want to display their name, along with the Titles of their most recent Book and their most recent Article. (Assume that the row with the largest BookId/ArticleId is the latest. Also assume that each author has at least one Book and one Article.)

In other words, each row would look something like this:

John Smith | Blacksmithing Handbook | Newest Tools for 2019

I can't figure out how to write the query for this.

SELECT AuthorName, BookTitle, ArticleTitle
FROM Authors
INNER JOIN Books ON Books.AuthorId = Authors.AuthorId
INNER JOIN Articles ON Articles.AuthorId = Authors.AuthorId
ORDER By AuthorName

This, of course, results in multiple rows for each Author, but I can't figure out how to get the result I want.

Any help would be greatly appreciated! (I'm using SQL Server 2016.)

Upvotes: 0

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is a case where outer apply can be quite useful:

SELECT a.AuthorName, b.BookTitle, ar.ArticleTitle
FROM Authors a OUTER APPLY
     (SELECT TOP (1) b.*
      FROM Books b
      WHERE b.AuthorId = a.AuthorId
      ORDER BY b.BookId DESC
     ) b OUTER APPLY
     (SELECT TOP (1) ar.*
      FROM Articles ar
      WHERE ar.AuthorId = a.AuthorId
      ORDER BY ar.ArticleId DESC
     ) ar
ORDER By a.AuthorName;

APPLY implements something called a "lateral join". This is (another) very powerful JOIN method. It is akin to correlated subqueries, but instead of being limited to a single column and a single row, it can return multiple columns and multiple rows.

Upvotes: 2

Related Questions