SQL: How to find the minimum number of entries linked from one table to another

Question

Let's say I have some Table_A:

A_id | A_val
1      a
2      b
3      c

Some Table_B:

B_id | B_val
1      d
2      e
3      g

and a linker Table_C:

A_id | B_id
1      1
2      1
2      2
3      1
3      2
3      3

I'm in need of help trying to find the items in Table A that has the fewest items in Table B linked to it. I'm currently a beginner with SQL using PostgreSQL and figured it may have something to do with using a sub-query. I've managed to count the links using:

SELECT A_id, COUNT(B_id) as Num_links
  FROM TABLE_C
  GROUP BY A_id;

But I've no idea where to go from here.

Answer 1

You could use a with clause to give an alias to your "count" query and treat it like a temp table. Then select the a_id with the num_links less-than-or-equal-to the lowest count in num_links.

WITH link_counts AS (
         SELECT a_id, COUNT(b_id) as num_links
           FROM table_c
       GROUP BY a_id
     )
SELECT a_id
  FROM link_counts
 WHERE num_links <= (SELECT MIN(num_links) FROM link_counts)

Note that this could return multiple rows if different a_id have same (lowest) number of links (for instance if a_id 1 and 4 both only had 1 link each).