0

I have tables Alpha and Beta. Beta belongs to Alpha.

create table Alpha
(
    id int auto_increment primary key
);

create table Beta
(
    id         int auto_increment primary key,
    alphaId    int null,
    orderValue int,
    constraint Alpha_ibfk_1 foreign key (alphaId) references Alpha (id)
);

Here are a few test records:

insert into Alpha (id) values (1);
insert into Alpha (id) values (2);
insert into Beta (id, alphaId, orderValue) values (1, 1, 23);
insert into Beta (id, alphaId, orderValue) values (2, 1, 43);
insert into Beta (id, alphaId, orderValue) values (3, 2, 73);

I want to create a pagination for them, that would make sense in terms of my application logic. So when I set limit 2, for example, I expect to get a list of two Alpha records and their related records, but in fact when I set limit 2:

    select *
from Alpha
      inner join Beta on Alpha.id = Beta.alphaId
order by Beta.orderValue
limit 2;

I am resulted with only one Alpha record and its related data:

![enter image description here

While I want to figure out a way for my LIMIT construct to only count unique occurrences of Alpha records and return me something like this:

enter image description here

Is it possible to do it in MySQL in one query? Maybe different RDBMS? Or going with multiple queries is the only option?

=== EDIT

The reason for such requirements is that I want to create an API with paging that returns records of Alpha, and their related Beta records. The problem is that the way limit works does not make sense from the user's standpoint: "Hey, I said I want 2 records of Alpha with its related data, not 1. What is that?"

4
  • What version of MySQL?
    – mustaccio
    Sep 3, 2021 at 12:59
  • The version is 5.7.32 Sep 3, 2021 at 13:08
  • Too bad. There'd be an easy solution if you were using a database that supports window functions.
    – mustaccio
    Sep 3, 2021 at 13:13
  • I would be happy if you told it to me! It would really help me in the future. Sep 3, 2021 at 13:17

2 Answers 2

2

If you were using a database that supports standard window functions, you could use dense_rank() to number distinct Alpha.ids:

select * from (
  select 
    a.id as a_id, b.id as b_id, b.alphaid,  
    dense_rank() over (order by a.id) as alpha_rank
  from Alpha a inner join Beta b on a.id=b.alphaid
) as t
where alpha_rank <= 2

Fiddle

With an earlier MySQL version that doesn't support window functions you could clumsily simulate that using variables:

select * from (
  select x.*
    ,@rank := case when a_id = @remember_a_id then @rank else @rank+1 end as alpha_rank
    ,@remember_a_id := a_id
  from
    (select a.id as a_id, b.id as b_id, b.alphaid
     from Alpha a 
     inner join Beta b on a.id=b.alphaid 
     order by a.id, b.id) as x
  cross join (select @rank:=0) as y
  cross join (select @remember_a_id:=null) as z
) as t
where alpha_rank <=2

though I suspect that the order might come out incorrect sometimes; you'll need to test that on your real data.

Fiddle

1

How to get 2 rows from Alpha (ordered by a.id) joined to all related rows from Beta:

select 
    a.*, b.*
from 
    ( select a.* 
      from Alpha as a
      order by a.id
      limit 2
    ) as a
    -- inner join Beta
    left join Beta as b    -- left join to get results from Alpha
    on a.id = b.alphaId    -- even if there are no related rows in Beta
order by 
    a.id,
    b.orderValue
 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.