how do I get the following postgres queries?

Question

I have two tables: tableA and tableB. TableA has a field idA and tableB has a record idB and idBPtrA where idBptrA is a pointer to tableA (one of the idA).

I want, using postgres, to select records from a TableA that have the minimal number of records in tableB.

Something like:

select idA,idB,count(idBPtrA) as c 
from tableA,tableB 
group by idBPtrA 
where idA=idB order by c

This of course doesn't work and gives me an error, but I think it should be very similar to that... Any ideas?

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-07-22T11:25:26

I think this is the query that you want:

select a.idA, count(b.idB) as c
from tableA a left join
     tableB b
     on a.idA = b.idptrA
group by a.idA 
order by c;

Notes:

Use table aliases and qualify column names (especially if you are learning SQL).
Learn and use proper, explicit JOIN syntax. Simple rule: Never use commas in the FROM clause.

how do I get the following postgres queries?

1 Answers