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
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2