I really need help with this subquery

[Mook Johnson]

Hey I am really bad at databases and I am having problems writing this query.

I have to write a subquery that lists the authors( au_id, fname and lname) and the title_Id for their books for authors that have books published by the publisher with the greatest sales. I have no idea how to do about doing this. I am horrible at sql queries. Please help. This is all I have :/

select authors.au_id,fname,lname,author_titles.title_id
   from authors left join author_titles on authors.au_id=author_titles.au_id

please help me. Thanks a lot!

[Rabbit]

After joining the author to the book, you just need to filter for the publisher with the most sales. That's where the subquery will come in, you just need a subquery to return the publisher id with the most sales.

[Mook Johnson]

Hey, thanks for the reply. I am still having problems with this. I am pretty bad when it comes to database. This is what I have now; Not really sure if I am on the right track but it returns 54 rows.. so I know it's incorrect.

SELECT authors.au_id,authors.fname,authors.lname,titles.title_id
   FROM authors left join author_titles ON authors.au_id = author_titles.au_id,titles
     WHERE pub_id IN(
     SELECT pub_id FROM titles
     WHERE sales=(SELECT MAX(sales)FROM titles));

Thanks for the help
*Mook*

[Rabbit]

Please use code tags when posting code.

The reason it's wrong is because you brought in a third table in your from clause without joining it.