# Limiting the number of records from nested INNER JOIN table

• The context is as follows:

• We have 3 tables: We'll call them A,B and C.
• A has a foreign key referencing table B, we'll call it "bId". The relationship is a ManyToOne. (One record in B can be linked to multiple ones in A, but each record in A is linked to exactly one in B)
• B has a foreign key referencing table C, we'll call it "cId". Same as before, the relationship is ManyToOne One record in C can be linked to multiple ones in B, but each record in B is linked to exactly one in C).

Now, the problem is as follows: Until now, to correlate information between these 3 tables I used a query of the form `A inner join B inner join C WHERE b.someField = 'x' AND a.otherField = 'y'`, and then processed the aggregated information using a programming language.

My question is, is it possible to limit the number of entries from the table C?

Let's say that C has a field called name, and I only want the data from the first 3 C values, alphabetically. A problem similar to mine seems to be the one answered https://dba.stackexchange.com/questions/120842/limiting-rows-from-a-joined-table , but I don't see how to generalise the solution.

To be clear, I don't want to limit the number of records in general, just the ones from the third table.

• This is a small example of how this could be achieved.

``````SELECT A.id,
A.otherfield,
B.id,
B.somefield,
C.id,
C.name
FROM   A
JOIN B
ON A.bid  = B.id
JOIN C
ON B.cid  = C.id
AND   b.someField  = 'x'
AND   a.otherField = 'y'
AND   C.name IN
(
SELECT C.name
FROM C
ORDER BY C.name DESC
LIMIT 3
)
;
``````

Example tables and data together with the statement for this answer can be found over on https://dbfiddle.uk/?rdbms=postgres_14&fiddle=594e39d15c91bd41ffd466a12b4e42ec .

Reproduced here in full:

``````CREATE TABLE A (id int, bid int, otherField varchar(10));
``````
```✓
```
``````CREATE TABLE B (id int, cid int, someField varchar(10));
``````
```✓
```
``````CREATE TABLE C (id int, AName varchar(10));
``````
```✓
```
``````INSERT INTO C
(id, AName)
VALUES
(1, 'FirstName'),
(2, 'SecondName'),
(3, 'ThirdName'),
(4, 'FourthName')
;
``````

4 rows affected

``````INSERT INTO B
(id, cid, someField)
VALUES (1,1,'x'),
(1,2,'x'),
(1,3,'x'),
(1,4,'x'),
(2,1,'x'),
(2,2,'x'),
(2,3,'y'),
(2,4,'y'),
(3,1,'x'),
(3,2,'z'),
(3,3,'y'),
(3,4,'y'),
(4,1,'y'),
(4,2,'x'),
(4,3,'x'),
(4,4,'x')
;
``````

16 rows affected

``````INSERT INTO A
(id,bid,otherField)
VALUES
(1,1,'x'),
(1,2,'x'),
(1,3,'x'),
(1,4,'x'),
(2,1,'x'),
(2,2,'x'),
(2,3,'y'),
(2,4,'y'),
(3,1,'x'),
(3,2,'z'),
(3,3,'y'),
(3,4,'y'),
(4,1,'y'),
(4,2,'x'),
(4,3,'x'),
(4,4,'x')
;
``````

16 rows affected

``````SELECT A.id,
A.otherField,
B.id,
B.someField,
C.id,
C.AName
FROM   A
JOIN B
ON A.bid  = B.id
JOIN C
ON B.cid  = C.id
AND   b.someField  = 'x'
AND   a.otherField = 'y'
AND   C.AName IN
(
SELECT C.AName
FROM C
ORDER BY C.AName DESC
LIMIT 3
)
;
``````

```id | otherfield | id | somefield | id | aname
-: | :--------- | -: | :-------- | -: | :---------
2 | y          |  4 | x         |  2 | SecondName
2 | y          |  4 | x         |  3 | ThirdName
2 | y          |  4 | x         |  4 | FourthName
3 | y          |  4 | x         |  2 | SecondName
3 | y          |  4 | x         |  3 | ThirdName
3 | y          |  4 | x         |  4 | FourthName
4 | y          |  1 | x         |  2 | SecondName
4 | y          |  1 | x         |  3 | ThirdName
4 | y          |  1 | x         |  4 | FourthName
```

2

2

2

2

2

2

2

2

2

2

2

2

2

2

2