Limiting the number of records from nested INNER JOIN table

Question

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 here, 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.

Answer 1

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 db<>fiddle (PostgreSQL 14).

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