SELECT with multiple subqueries to same table

sql sql-server tsql subquery

71,714

Solution 1

If you are using SQL Server 2005 and later, you can use a ranking function like so:

With LastCheckout As
    (
    Select StudentId, BookName, BookAuthor, DateCheckedOut 
        , Row_Number() Over ( Partition By StudentId Order By DateCheckedOut Desc) As CheckoutRank
    From TBookCheckouts
    )
Select ..., LastCheckout.BookName, LastCheckout.BookAuthor, LastCheckout.DateCheckedOut
From TStudents
    Left Join LastCheckout 
        On LastCheckout.StudentId = TStudents.StudentId
                And LastCheckout.CheckoutRank = 1

Solution 2

On 2005 and higher, OUTER APPLY is your friend:

SELECT TStudents.*,
       t.BookName ,
       t.BookAuthor ,
       t.BookCheckout
   FROM TStudents
  OUTER APPLY(SELECT TOP 1 s.* 
                     FROM TBookCheckouts AS s
                    WHERE s.StudentID = TStudents.ID 
                 ORDER BY s.DateCheckedOut DESC) AS t

Solution 3

Use:

   SELECT s.*,
          x.bookname,
          x.bookauthor,
          x.datecheckedout
     FROM TSTUDENTS s
LEFT JOIN (SELECT bc.studentid,
                  bc.bookname,
                  bc.bookauthor,
                  bc.datecheckedout,
                  ROW_NUMBER() OVER(PARTITION BY bc.studentid
                                        ORDER BY bc.datecheckedout DESC) AS rank
             FROM TSBOOKCHECKOUTS bc) x ON x.studentid = s.id
                                       AND x.rank = 1

If the student has not checkout any books, the bookname, bookauthor, and datecheckedout will be NULL.

71,714

Author by

marie

Made with real slurm ☃

Updated on August 23, 2020

Comments

marie almost 4 years
I'm using the same SQL pattern over and over, and I know there has to be a better way, but I'm having trouble piecing it together. Here's a simple version of the pattern, where I'm pulling back the student's information and the last book they checked out, if one exists:
```
SELECT TStudents.*,
       BookName = (SELECT TOP 1 BookName 
                     FROM TBookCheckouts 
                    WHERE StudentID = TStudents.ID 
                 ORDER BY DateCheckedOut DESC),
       BookAuthor = (SELECT TOP 1 BookAuthor 
                       FROM TBookCheckouts 
                      WHERE StudentID = TStudents.ID 
                   ORDER BY DateCheckedOut DESC),
       BookCheckout = (SELECT TOP 1 DateCheckedOut 
                         FROM TBookCheckouts 
                         WHERE StudentID = TStudents.ID 
                     ORDER BY DateCheckedOut DESC)
   FROM TStudents
```
(For the sake of this example, please ignore the fact that TBookCheckouts should probably be split into TCheckouts and TBooks)

What I'm trying to illustrate: I tend to have a lot of subqueries for columns from the same table. I also tend to need to sort those subqueried tables by a date to get the most recent record, so it's not quite as simple (at least to me) as doing a LEFT JOIN. Notice, though, that except for which field is being returned, I'm essentially doing the same subquery 3 times. SQL Server may be smart enough to optimize that, but I'm thinking not (I definitely need to get better at reading execution plans...).

While there might be advantages to structuring it this way (sometimes this ends up being more readable, if I have tons of subqueries and sub-tables), it doesn't seem like this is particularly efficient.

I've looked into doing a LEFT JOIN from a derived table, possibly incorporating a ROW_NUMBER() and PARTITION BY, but I just can't seem to piece it all together.
marie over 13 years

Close, but this won't give me the TOP 1 part. I need one record per student.
marie over 13 years

Won't this only return one record total? I'm looking for 1 record per student.
p.campbell over 13 years

@Jon: here's an update. I've kept the same names from my test database, and not used the same names, as it appears you're obfuscated/mocked in your question. Hope this helps!
LittleBobbyTables - Au Revoir over 13 years

That really doesn't solve his problem as you state You get multiple BookCheckouts per student if there are 2 or more Bookcheckouts for a Student with the same, max checkout date.
nang over 13 years

@LittleBobbyTables: Added another solution without duplicates
Noel Abrahams over 13 years

@LittleBobbyTables, fixed the compilation errors. I believet this should now work.
LittleBobbyTables - Au Revoir over 13 years

But it's missing students that have never checked out a book
LittleBobbyTables - Au Revoir over 13 years

The query returns the student multiple times if they have multiple books checked out in the same day.
Noel Abrahams over 13 years

Yes, it needed a DISTINCT which I've added. But some of the other solutions are more compact, so really just for the record.
marie over 13 years

I like the CTE; I may use one, depending on the query. I wish I could give out two accepted answers.
marie over 13 years

Actually I'm switching the accepted answer to you, since I think the CTE solution works much more nicely. Thanks!
OMG Ponies over 13 years

@Jon Smock: There's no performance improvement to using a CTE, but it is cleaner to read.
marie over 13 years

@OMG Ponies That's basically why I switched. I actually started with your solution, but once I got it working, the first thing I did was rewrite it with the CTE to clean it up. Like I said, I really wish I could give two accepted answers, especially since you both submitted with minutes of each other. I did give you a plus 1 :-P
nang over 13 years

I considered adding a left join with TStudent obvious.
Thomas over 13 years

@Jon Smock, @OMG Ponies - Ty for the upvote. As OMG Ponies said, there is no perf difference. It's just a bit more readable.