CODEWITHSUNDEEP
sqlsql-servert-sql
user31673
user31673

Reputation: 13685

How to write the T-SQL code for a query

I need to use t-sql to query two tables. The first table is Books. The second table is Authors. For each Book record there could be multiple child Author records. I want to write a query that only returns the first Author record found for the current Book record. There are hundreds of thousands of records in the tables so I need the query to be efficient.

select a.FirstName, a.LastName, b.BookName
from Books b
left join 
(
    select TOP 1 t.BookID, t.FirstName, t.LastName 
    from Authors t
) a 
    on a.BookID = b.BookID
where b.BookClassification = 2

This query is not right. I only want to select the top 1 record in the Authors which match the BookID. How can I get the results I am looking for?

Upvotes: 1

Views: 236

Answers (3)

RBarryYoung
RBarryYoung

Reputation: 56725

You were close:

select a.FirstName, a.LastName, b.BookName
from Books b
outer apply 
(
    select TOP 1 t.BookID, t.FirstName, t.LastName 
    from Authors t
    WHERE t.BookID = b.BookID
    -- uncomment the next line to control which author to prefer
    -- ORDER BY t.<someColumn>...
) a 
where b.BookClassification = 2

Though it seems odd to me that Authors would be a child of Books... :)

Upvotes: 6

jason saldo
jason saldo

Reputation: 9950

In the spirit of TIMTOWTDI.

You can use a CTE, a fancy subquery but helpful if the subquery is used more than once. And one the rank functions, row_number().

with bookAuthors as ( 
  select a.FirstName, a.LastName, b.BookName, BookClassification, 
    row_number() over(partition by b.BookName order by a.lastName ) as rank
  from Books b
  left join Authors a
    on a.BookID = b.BookID

)

select a.FirstName, a.LastName, b.BookName
from bookAuthors 
where rank = 1
  and BookClassification = 2

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

See if this is more efficient. By looking for min(authorID) just once you might get better performance.

select author.FirstName, author.LastName, author.BookName    
from Books with (nolock) 
join 
(   select min(authorID) as authorID, bookID 
    from Authors with (nolock) 
    group by bookID 
)   as Author1
  on Author1.authorID = Books.authorID 
join Authors with (no lock) 
 on  Authors.authorID = Author1.authorID 
 and Authors.bookID = Author1.bookID
where Books.BookClassification = 2

Upvotes: 0

Related Questions