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: 175
sql-server
|sql-server-2000
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