SQL Subquery question for assignment?

Question

The question is:

List the authors ho have written at least one "Popular Computing" book. Do NOT use a Join. Show AuthorID and their name.

here is what I have for a query so far..

select au_id [Author ID], au_fname + ' ' + au_lname [Author's Name]
from authors
where.......????

The database has 2 tables for this question. First table (authors) stores the author information. The second table (titles) stores the type of book being sold (type). I'm not sure how to reference the title table through a subquery on this question. Any help is appreciated.

Answer 1

I find the "IN" statement with subqueries to be horrid on some DB/s, and also that's a mighty interesting assignment constraint to not use joins... oh well. This doesn't use the JOIN verb, but it does check for au_id.titles = table.titles; You might want to try:



SELECT au_id S "Author ID", au_fname + ' ' + au_lname AS "Author's Name", count(*)

FROM authors, table

WHERE table.type = "Popular Computing" AND au_id.titles = table.titles

HAVING count(*) >= 1

Answer 2

The above will work but requires the subquery be evaluated for every row of the primary query...I prefer the method below as it only requires the subquery be evaluated once (to build the criteria list):



SELECT au_id AS "Author ID", au_fname + ' ' + au_lname AS "Author's Name"

FROM authors WHERE au_id IN

(SELECT DISTINCT au_id FROM titles WHERE type = "Popular Computing")

Answer 3

select au_id [Author ID], au_fname + ' ' + au_lname [Author's Name]

from authors

where exists (select 1 from titles where type='Popular Computing' and titles.au_id = authors.au_id)