How can I exclude LEFT JOINed tables from TOP in SQL Server?

Posted by Kalessin on Stack Overflow See other posts from Stack Overflow or by Kalessin
Published on 2012-04-16T08:09:51Z Indexed on 2012/04/16 11:29 UTC
Read the original article Hit count: 178

Filed under:
|

Let's say I have two tables of books and two tables of their corresponding editions.

I have a query as follows:

SELECT TOP 10 * FROM
(SELECT hbID, hbTitle, hbPublisherID, hbPublishDate, hbedID, hbedDate
 FROM hardback
 LEFT JOIN hardbackEdition on hbID = hbedID
 UNION 
 SELECT pbID, pbTitle, pbPublisher, pbPublishDate, pbedID, pbedDate
 FROM paperback
 Left JOIN paperbackEdition on pbID = pbedID
) books
WHERE hbPublisherID = 7
ORDER BY hbPublishDate DESC

If there are 5 editions of the first two hardback and/or paperback books, this query only returns two books. However, I want the TOP 10 to apply only to the number of actual book records returned. Is there a way I can select 10 actual books, and still get all of their associated edition records?

In case it's relevant, I do not have database permissions to CREATE and DROP temporary tables.

Thanks for reading!

Update

To clarify: The paperback table has an associated table of paperback editions. The hardback table has an associated table of hardback editions. The hardback and paperback tables are not related to each other except to the user who will (hopefully!) see them displayed together.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2000