Is there something wrong with this SQL query

Question

I am new to SQL queries and trying to understand it a little better. Is this SQL query valid regarding the picture below The question I was given was:

"For each book authored (or co-authored) by "Stephen King", retrieve the title and the number of copies owned by the librarybranch whose name is "Central"

SELECT Title
FROM BOOK
WHERE BookID = 
(
 SELECT BookID
 FROM BOOK_AUTHORS
 WHERE AuthorName = 'Stephen King'
)
AND
BookID = 
(
 SELECT BookID, No_Of_Copies
 FROM BOOK_COPIES
 WHERE BranchID =
 (
  SELECT BranchID
  FROM LIBRARY_BRANCH
  WHERE BranchName = 'Central'
 )
)

I have not set up any environment and our lecturer hasn't told us how to at all, we are suppose to work off of intuition, and I have no way of knowing if this works or not because he hasn't answered my question whether this is correct or not and how to fix it if it's not valid. — TyperMan1133, Jun 02 '20 at 11:55
You can only have one column in the sub `SELECT` so it will not work to write `BookID = ( SELECT BookID, No_Of_Copies...` You will need to use `JOIN` `COUNT` and `GROUP BY` — mortb, Jun 02 '20 at 11:56
Then build sample datas, otherwise you will never be able to test your queries — Cid, Jun 02 '20 at 11:57
Please use text, not images/links, for text. Eg here. But--Knowledge of keys is not needed to query. Necessary & sufficient is to know for each table--base & query result--its meaning--what a row in it says about the business situation in terms of column values. (But when constraints hold, a given result is returned by more expressions than otherwise.) [Re SQL querying.](https://stackoverflow.com/a/33952141/3404097) And--Informing yourself re using the site [help] [ask] says code questions need a [mre]. Also that you can test it at sqlfiddle.com. Also you could just google 'run sql online'. — philipxy, Jun 02 '20 at 23:03

score 1 · Accepted Answer · answered Jun 02 '20 at 11:53

Please use below query, but the right approach is to use joins instead of multiple sub queries

Joins

SELECT B.Title
FROM BOOK B
inner join BOOK_AUTHORS BA
on (B.bookid = ba.bookid)
inner join BOOK_COPIES BC
on (B.bookid = BC.bookid)
inner join LIBRARY_BRANCH LB
on (BC.branchid = LB.branchid)
where B.AuthorName = 'Stephen King'
and LB.BranchName = 'Central';

Subquery

SELECT Title
FROM BOOK
WHERE BookID in
(
 SELECT BookID
 FROM BOOK_AUTHORS
 WHERE AuthorName = 'Stephen King'
)
AND
BookID in 
(
 SELECT BookID
 FROM BOOK_COPIES
 WHERE BranchID in
 (
  SELECT BranchID
  FROM LIBRARY_BRANCH
  WHERE BranchName = 'Central'
 )
);

score 1 · Answer 2 · answered Jun 02 '20 at 11:57

Your version obviously does not answer the question because it does not return a count.

The most common way to approach a problem like this uses JOINs and GROUP BY:

SELECT b.Title, COUNT(lb.branch_id)
FROM BOOK_AUTHORS ba JOIN
     BOOK b
     ON ba.BookID = b.BookId LEFT JOIN
     BANK_COPIES bc
     ON ba.book_id = b.book_id LEFT JOIN
     LIBRARY_BRANCH lb
     ON lb.branch_id = bc.branch_id AND
        lb.BranchName = 'Central'
WHERE ba.AuthorName = 'Stephen King'
GROUP BY ba.BookID, ba.Title;

Note the use of LEFT JOIN. The question appears to want all titles, even those with no copies.

No need to `GROUP BY`, the number of copies is already in the table `BOOK_COPIES` — Cid, Jun 02 '20 at 11:58

score 1 · Answer 3 · answered Jun 02 '20 at 12:01

For this requirement you can use joins to get the results:

SELECT b.Title, bc.No_Of_Copies
FROM BOOK b 
INNER JOIN BOOK_AUTHORS ba ON ba.BookId = b.BookId
INNER JOIN BOOK_COPIES bc ON bc.BookId = b.BookId
INNER JOIN LIBRARY_BRANCH lb ON lb.BranchId = bc.BranchId
WHERE ba.AuthorName = 'Stephen King' AND lb.BranchName = 'Central'

Is there something wrong with this SQL query

3 Answers3