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.

Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking. — Community, Jan 11 '22 at 15:27
[Edit] the question and provide a [example], i.e. the CREATE statements of the tables and/or other objects involved (paste the text, don't use images, don't link to external sites), INSERT statements for sample data (dito) and the desired result with that sample data in tabular text format. — sticky bit, Jan 12 '22 at 01:11
Thank you for the feedback, I will do so in future questions. But it seems that John K. N. 's answer provides such a context and a solution that achieves what I was trying to do. — Robert Tabacaru, Jan 12 '22 at 15:57

score 2 · Accepted Answer · answered Jan 12 '22 at 10:53

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

Limiting the number of records from nested INNER JOIN table

1 Answers1