Selecting X amount of rows from one table depending on value of column from another joined table-CodePudding

I am trying to join several tables. To simplify the situation, there is a table called Boxes which has a foreign key column for another table, Requests. This means that with a simple join I can get all the boxes that can be used to fulfill a request. But the Requests table also has a column called BoxCount which limits the number of boxes that is needed.

Is there a way to structure the query in such a way that when I join the two tables, I will only get the number of rows from Boxes that is specified in the BoxCount column of the given Request, rather than all of the rows from Boxes that have a matching foreign key?

Script to initialize sample data:

CREATE TABLE Requests (
  Id int NOT NULL PRIMARY KEY,
  BoxCount Int NOT NULL);
  
  CREATE TABLE Boxes (
  Id int NOT NULL PRIMARY KEY,
  Label varchar,
  RequestId INT FOREIGN KEY REFERENCES Requests(Id));
  
  INSERT INTO Requests (Id, BoxCount)
  VALUES 
  (1, 2),
  (2, 3);
  
  INSERT INTO Boxes (Id, Label, RequestId)
  VALUES 
  (1, 'A', 1),
  (2, 'B', 1),
  (3, 'C', 1),
  (4, 'D', 2),
  (5, 'E', 2),
  (6, 'F', 2),
  (7, 'G', 2);

So, for example, when the hypothetical query is ran, it should return boxes A and B (because the first Request only needs 2 boxes), but not C. Similarly it should also include boxes D, E and F, but not box G, because the second request only requires 3 boxes.

CodePudding user response：

The INNER JOIN keyword selects records that have matching values in both tables

SELECT (cols) FROM Boxes
INNER JOIN Request on Boxes.(FK_column) = request.id
WHERE Request.BoxCount = (value)

CodePudding user response：

Here is another approach using ROWCOUNT - a common and useful technique that every sql writer should master. The idea here is that you create a sequential number for all boxes within a request and use that to compare to the box count for filtering.

with boxord as (select *, 
                ROW_NUMBER() OVER (PARTITION BY RequestId ORDER BY Id) as rno
                from dbo.Boxes 
                )
select req.*, boxord.Label, boxord.rno
from dbo.Requests as req inner join boxord on req.Id = boxord.RequestId
where req.BoxCount >= boxord.rno
order by req.Id, boxord.rno
;

fiddle to demonstrate

CodePudding user response：

select      r.id, 
            r.boxcount, 
            b.id, 
            b.label
from        requests      r
cross apply (
               select   top (r.BoxCount) 
                        id, label
               from     boxes
               where    requestid = r.id
               order by id 
            ) b;