How do I solve this subquery with case expression?

Question

***Rules: --- Do not use a join of any kind and do not use a correlated subquery. --- Do not use a comma join. --- If you use a join, you will get no points for that task. A From clause will reference only one table. --- DO not use a variable.

***Here is my problem: For each book in the books table that includes Bird in the book title, display the book ID and title and a message as to whether or not we have any orders for that book. Do not use the Count() function. The output will follow this format and be sort ed by the OrderStatus column with the books with no order first; the second sort key is the book id.

Here is the solution using count() function and correlated subquery. How do I solve this problem using neither? Remember, I can not use a join either.

SELECT book_id, title 
, CASE (
 SELECT count(*)
 FROM a_bkorders.order_details OD
 WHERE OD.book_id = BK.book_id
   )
 when 0 then 'no orders' 
 when 1 then 'Have orders'        
 when 2 then 'Have orders'   
 when 3 then 'Have orders'        
 else  'Have orders'        
 End as OrderStatus
From a_bkinfo.books BK
WHERE title Like '%Bird%'
ORDER BY OrderStatus, book_id;

Not sure this is possible, as without a join or correlated sub query you are left only accessing one table. You could use a non correlated sub query, but you would need to join that against the books table. — Kickstart, Nov 04 '13 at 09:58

score 0 · Answer 1 · answered Nov 04 '13 at 10:24

0

SELECT BookId,Title, COUNT(BookId),(IF (COUNT(BookId))>0,"No Order","Have Orders") AS Status FROM a_bkinfo.books BK WHERE title LIKE '%Bird%' GROUP BY BookId ORDER BY book_id

answered Nov 04 '13 at 10:24

Agoeng Liu

674
3
10
30

score 0 · Answer 2 · answered Nov 04 '13 at 11:47

try this query:

SELECT book_id, title 
, CASE when (SELECT count(*) FROM a_bkorders.order_details OD WHERE OD.book_id = BK.book_id) = 0
then 'no orders' 
       when (SELECT count(*) FROM a_bkorders.order_details OD WHERE OD.book_id = BK.book_id) = 1 
then 'Have orders'        
       when (SELECT count(*) FROM a_bkorders.order_details OD WHERE OD.book_id = BK.book_id) = 2
then 'Have orders'   
       when (SELECT count(*) FROM a_bkorders.order_details OD WHERE OD.book_id = BK.book_id) = 3 
then 'Have orders'        
else  'Have orders'        
End as OrderStatus
From a_bkinfo.books BK
WHERE title Like '%Bird%'
ORDER BY OrderStatus, book_id;

How do I solve this subquery with case expression?

2 Answers2