Geek asks and answers

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

Say I have two tables of books and two tables of their respective issues.

I have a request:

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 issues of the first two hardback and / or paperback books, this query returns only two books. However, I want to TOP 10apply only to the number of actual book entries returned. Is there a way that I can select 10 actual books and still get all the related publication notes?

In case that matters, I don't have database permissions for the temporary tables CREATE and DROP.

Thanks for reading!

Update

: . . , , () .

+3
3

. Top 10 . .

, hbID pbID . , . , .

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 = 7
  ORDER BY publishDate DESC
  ) topTen 
  on books.id = TopTen.id
0

, 10

  • WITH ,
  • 10 , GROUP BY
  • JOIN , 10 .

SQL

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

where

;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
          GROUP BY
                hbID
        ) bt ON bt.hbID = b.hbID
+1

This should capture the last ten published hardback titles from publisher 7:

select  *
from    (
        select  top 10 title
        from    hardback
        where   hbPublisherID = 7
        group by
                title
        order by
                hbPublishDate desc
        ) top_titles
left join
      hardback
on    hardback.hbTitle = top_titles.title
left join
      paperback
on    paperback.pbTitle = top_titles.title
0
source

More articles:


All Articles