CODEWITHSUNDEEP
mysqlsql
Dewie
Dewie

Reputation: 17

Subquery confusion in SQL

Using just a basic SQL program to run small little stuff so the answer should be pretty simple and small. But have database with couple of tables one being labeled:

tAuthors with fAuthorID and fAuthorName, next I have tBooks with fAuthorID , fPubID....etc (thinking only going to be using one of those two). and have tPublishers with fPubID and fPubName.

So what I have been trying to do is list the names of all authors who have a book published by the publisher with ID number 12;list the author names in alphabetical order. I got the alphabetical part down but can seem to get the correct authors names. This is what I got but it is only pulling one author and I believe here are 7 authors total with the ID number 12 attached to them.

 SELECT `fAuthorName`, `fAuthorID`
 FROM `tAuthors`
 WHERE `fAuthorID` IN (
     SELECT `fPubID`
     FROM `tPublishers`
     WHERE `fPubID` = 12
 )
 ORDER BY `fAuthorName` ASC;

Upvotes: 0

Views: 41

Answers (2)

Andrej
Andrej

Reputation: 7504

You can do it with following query using tBooks instead of tPublishers:

 SELECT `fAuthorName`, `fAuthorID`
 FROM `tAuthors`
 WHERE `fAuthorID` IN (
     SELECT `fAuthorID`
     FROM `tBooks`
     WHERE `fPubID` = 12
 )
 ORDER BY `fAuthorName` ASC;

Upvotes: 0

Niles Tanner
Niles Tanner

Reputation: 4041

Might be easier to do a join. Authors table connects to Books table by the author id and the books table connects to the publishers table by the publisher id. Once they are all joined you can just filter by pub id and sort.

 SELECT `a.fAuthorName`, `a.fAuthorID`
 FROM `tAuthors` a
 JOIN `tBooks` b ON (a.fAuthorID = b.fAuthorID )
 JOIN `tPublishers` p ON (b.fPubID = p.fPubID)
 WHERE `p.fPubID` = 12
 ORDER BY `a.fAuthorName` ASC;

Upvotes: 2

Related Questions