Dewie Dewie - 2 months ago 19
MySQL Question

Subquery confussion in SQL

Ok here is the question...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;


Any one out there that can steer me in the right way that would be awesome.

Answer

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; 
Comments