MYSQL nested inner join query

Question

I wanted to create a nested query that on the outside gets the title and price from a table called 'Books' while having a nest inside the query that gets the Author's First and last name for that specific book. I'm just a little confused on the Inner Joins and where they need to be placed. This is as close as I was able to get with it but this just prints every author for every book.

select Title, AuthorFirst, AuthorLast,Price from Book
JOIN
(select AuthorLast,AuthorFirst from Author 
INNER JOIN Wrote on Author.AuthorNum = Wrote.AuthorNum 
INNER JOIN Book on Wrote.BookCode = Book.BookCode group by title desc)Auth;

This joins the tables that I need but it prints every author in the DB with every book in the database. I think it has something with my Inner Joins not being specific enough.

A minimal reproducible example includes DBMS (with version), a clear specification, cut & paste & runnable code (with small representative input) (format table initializations as tables) & diffable (hence ordered) desired output. But absolute basics of debugging say: Show that your program does what you expect as it goes through (sub)expressions by saying what that is & showing that it actually does it via incremental output. On adding problem code that you can't fix, research (the manual & the web). Repeat, minimizing working & wrong code. Then ask re the (small) difference between working & non-working examples. — philipxy
– philipxy, Commented Dec 4, 2018 at 6:57
Why do you specifically need to use a nested query ? Looks like you don’t need that to achieve your goal — GMB
– GMB, Commented Dec 4, 2018 at 8:02

Mureinik · Accepted Answer · 2018-12-04 06:44:45Z

1

The group by clause is wrong and you should remove it. Once you do that, there's no need to nest the joins - you could just have several joins in the same query:

SELECT     Title, AuthorFirst, AuthorLast, Price
FROM       Book
INNER JOIN Wrote ON Author.AuthorNum = Wrote.AuthorNum 
INNER JOIN Book ON Wrote.BookCode = Book.BookCode

answered Dec 4, 2018 at 6:44

Mureinik

316k54 gold badges400 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Trevor Johnson Over a year ago

It is required that I nest unfortunately or else I would already have this done

Collectives™ on Stack Overflow

MYSQL nested inner join query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related