How Can I Exclude Left Joined Tables From Top In Sql Server?

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, hbPublish

Solution 1:

If I understand you correctly, you could get the 10 books with all associated editions by

• Using a WITH statement to return the initial, complete resultset
• select 10 distinct books by using a GROUP BY
• JOIN the results of this group to retain all information from given 10 books.

SQL Statement

;WITH books AS (
  SELECT  hbID, hbTitle, hbPublisherID, hbPublishDate, hbedID, hbedDate
  FROM    hardback
          LEFTJOIN hardbackEdition on hbID = hbedID
  WHERE   hbPublisherID =7UNIONALLSELECT  pbID, pbTitle, pbPublisher, pbPublishDate, pbedID, pbedDate
  FROM    paperback
          LEFTJOIN paperbackEdition on pbID = pbedID
  WHERE   hbPublisherID =7
)
SELECT*FROM    books b
        INNERJOIN (
          SELECT TOP 10 hbID
          FROM   books
          GROUPBY
                hbID
        ) bt ON bt.hbID = b.hbID

or if you prefer to write the where clause only once

;WITH books AS (
  SELECT  hbID, hbTitle, hbPublisherID, hbPublishDate, hbedID, hbedDate
  FROM    hardback
          LEFT JOIN hardbackEdition on hbID = hbedID
  UNION ALL
  SELECT  pbID, pbTitle, pbPublisher, pbPublishDate, pbedID, pbedDate
  FROM    paperback
          LEFT JOIN paperbackEdition on pbID = pbedID
)
, q AS (
  SELECT  *
  FROM    books
  WHERE   hbPublisherID = 7          
)
SELECT  *
FROM    q b
        INNER JOIN (
          SELECT TOP 10 hbID
          FROM   q
          GROUPBY
                hbID
        ) bt ON bt.hbID = b.hbID

Solution 2:

Not so easy. You need to apply Top 10 to only the hardback and paperback tables, without the join. Then join the result to the data.

The following query only works when the hbID and pbID are always unique. If not, it gets more complicated. You need to separate them or add another column to the query to distinguish them.

SELECT *
FROM
  (SELECT hbID as id, hbTitle, hbPublisherID, hbPublishDate, hbedID, hbedDate
   FROM hardback
   LEFT JOIN hardbackEdition on hbID = hbedID
   UNION 
   SELECT pbID as id, pbTitle, pbPublisher, pbPublishDate, pbedID, pbedDate
   FROM paperback
   Left JOIN paperbackEdition on pbID = pbedID
  ) books
INNER JOIN 
  (SELECT TOP 10 * 
  FROM
    (SELECT hbID as id, hbPublisherID as publishedId, hbPublishDate as publishDate
     FROM hardback
     UNION 
     SELECT pbID as id, pbPublisherID as publishedId, pbPublishDate as publishDate
     FROM paperback
    ) 
  WHERE publisherID = 7ORDERBY publishDate DESC
  ) topTen 
  on books.id = TopTen.id

Solution 3:

This should grab the ten most recently published titles with a hardback from publisher 7:

select  *
from    (
        select  top 10 title
        from    hardback
        where   hbPublisherID = 7groupby
                title
        orderby
                hbPublishDate desc
        ) top_titles
left join
      hardback
on    hardback.hbTitle = top_titles.title
left join
      paperback
on    paperback.pbTitle = top_titles.title