I'm trying to wrap my head around how to do a proper subquery, it's not making sense to me, lets say I have two tables books and chapters:
Books
+----+------------------+----------+---------------------+
| id | name | author | last_great_chapters |
+----+------------------+----------+---------------------+
| 1 | some book title | john doe | 2 |
| 2 | foo novel title | some guy | 4 |
| 3 | other book title | lol man | 3 |
+----+------------------+----------+---------------------+
Chapters
+----+---------+----------------+
| id | book_id | chapter_number |
+----+---------+----------------+
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 1 | 4 |
| 4 | 1 | 5 |
| 5 | 2 | 1 |
| 6 | 2 | 2 |
| 7 | 2 | 3 |
| 8 | 2 | 4 |
| 9 | 2 | 5 |
| 10 | 3 | 1 |
| 11 | 3 | 2 |
| 12 | 3 | 3 |
| 13 | 3 | 4 |
| 14 | 3 | 5 |
+----+---------+----------------+
How can I join the two tables, and just print out the number of rows (sorted limit(last_great_chapters)) of the "last_great_chapters" from the books table list for each book?
if I understood correctly, you want to print out table books and last_great_chapters count in Chapters table?
if yes, try it
select b.id, b.name, b.author , b.last_great_chapter, COUNT(c.chapter_number) as rownumbers FROM Books as b
LEFT JOIN Chapters AS C ON c.chapter_number = b.last_great_chapters
group by b.id, b.name, b.author , b.last_great_chapter