Need help with a conditional SELECT statement

Question

I've got a stored procedure with a select statement, like this:

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

This limits results to authors who have book records. This is the Books table:

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

What I want to do is conditionally select the ID of the featured book by each author as part of the select statement above, and if none of the books for a given author are featured, select the ID of the first (top 1) book by the author from the books table. How do I approach this?

Answer 1

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

Something along the lines of,

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) 

Answer 2

try this:

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

OUTPUT:

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

(3 row(s) affected)

Answer 3

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

Answer 4

Relation doesn't have ordering in theory. So "the first book" ideally should be specified by some aggregation rule. If you satisfied by "min(bookID)" then something like that:

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