Geek Answers Handbook

Need help with conditional SELECT statement

I have a stored procedure with a select statement, for example:

SELECT author_ID,
       author_name,
       author_bio
FROM   Authors
WHERE author_ID in (SELECT author_ID from Books)

This limits the results for authors who have book entries. This is the Books table:

Books:
book_ID INT,
author_ID INT,
book_title NVARCHAR,
featured_book BIT

What I want to do is to conditionally select the identifier of the recognized book by each author as part of the above select statement, and if none of the books for this author is specified, select the first identifier (top 1) of the author’s book from the book table. How do I approach this?

+5
source share
4 answers

You can use a helper query that orders data and uses the top statement ...

Something along the lines

SELECT  author_ID, 
         author_name, 
         author_bio
         , (Select top 1 Book_ID from Books where Authors.Author_ID=Books.Author_ID order by Featured_book desc)
 FROM    Authors 
 WHERE author_ID in (SELECT author_ID from Books)
+1
source

:

DECLARE @Authors table (author_ID INT NOT NULL, author_name VARCHAR(100) NOT NULL, author_bio VARCHAR(100) NOT NULL)
INSERT INTO @Authors VALUES (1, 'Author1', 'Bio1')
INSERT INTO @Authors VALUES (2, 'Author2', 'Bio2')
INSERT INTO @Authors VALUES (3, 'Author3', 'Bio3')

DECLARE @Books table (book_ID INT NOT NULL, author_ID INT NOT NULL, book_title VARCHAR(100) NOT NULL, featured_book INT NOT NULL)
INSERT INTO @Books VALUES (1, 2, 'Book1', 0)
INSERT INTO @Books VALUES (2, 2, 'Book2', 1)
INSERT INTO @Books VALUES (3, 3, 'Book3', 0)
INSERT INTO @Books VALUES (4, 3, 'Book4', 0)

SELECT
    dt.author_ID, dt.author_name,dt.author_bio,dt.book_title
    FROM (
          SELECT
              a.*,b.book_title,ROW_NUMBER() OVER (PARTITION by a.author_ID ORDER BY b.featured_book DESC,b.book_title) RowNumber

          FROM Authors              a
              LEFT OUTER JOIN Books b ON a.author_id = b.author_id
          ) dt
WHERE dt.RowNumber= 1

:

author_ID   author_name  author_bio  book_title
----------- ------------ ----------- ----------
1           Author1      Bio1        NULL      
2           Author2      Bio2        Book2     
3           Author3      Bio3        Book3      

(3 row(s) affected)
+1
Select X.*, BB.book_title from 
(
    SELECT A.author_ID, A.author_name, A.author_bio,
      (Select top 1 book_ID from Books B WHERE B.author_ID = A.author_ID
        order by B.featured_book, book_Title) as book_ID, BB.book_Title
    FROM   Authors A 
)X right join Books BB on X.book_id = BB.book_ID
0

The relation is not ordered in theory. Therefore, the “first book” should ideally be indicated by some aggregation rule. If you are satisfied with "min (bookID)", then something like this:

SELECT Authors.author_ID, author_name, author_bio,
  CASE max(featured_book)
  WHEN 0 THEN min(book_ID)
  END
FROM Authors INNER JOIN
     Books ON Authors.author_ID = Books.author_ID
GROUP BY Authors.author_ID, author_name, author_bio
0
source

More articles:


All Articles