kloop kloop - 4 months ago 7
SQL Question

how do I get the following postgres queries?

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?

Answer

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.