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
