CODEWITHSUNDEEP
mysqlsql
Syed
Syed

Reputation: 71

MySQL Inner Join Limit Result From Second Table

I have following data in my table.

Table Books
Id | Book_Name
1  | Java Programming
2  | PHP Programming
3  | HTML5

Table Chapters
Book Id | Chapter_Number
1       | Chapter 1
1       | Chapter 1v2
1       | Chapter 1v3
2       | Chapter 2 Release 1
2       | Chapter 2 Rev.
3       | Chapter 1
4       | Chapter 1

What I wanted to achieve is to limit Chapter Number data return from each Book Chapters only to 2 rows.

Example of data that I want

Book Id | Book Name | Chapter_Number
1  | Java Programming | Chapter 1
1  | Java Programming | Chapter 1v2
2  | Java Programming | Chapter 2 Release 1
2  | Java Programming | Chapter 2 Rev.
3  | Java Programming | Chapter 1
4  | Java Programming | Chapter 1

Is there a way to get this?

Upvotes: 2

Views: 1130

Answers (2)

Mark
Mark

Reputation: 5642

I'm not sure how you could do this using just SQL. You could do it in SQL with the help of some code like this:

First, get a list of chapter ids:

select distinct id from Chapter;

Then you'd have to iterate (in code) over each id, running a query such as this:

select * from Chapter join Book on Book.id = Chapter.Book_id where Chapter.Book_id=(the iterated id) limit 2;

Obviously this isn't very scalable because you have to run a query for each id in the list. It's a good question. I'll vote it up. Maybe someone can provide an SQL-only answer.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62861

One way to do this is to introduce a rank column. You can do this with mysql by using user-defined variables:

select *
from (
  select b.id, 
         b.book_name, 
         c.chapter_number,
         @rnk:=IF(@prevbookid=b.id,@rnk+1,1) rnk,
         @prevbookid:=b.id
  from books b
    join chapters c on b.id = c.bookid
    join (select @rnk:= 0, @prevbookid:= 0) t
  order by b.id, c.chapter_number
  ) t
where rnk <= 2

Upvotes: 4

Related Questions