sqloracle-databaseleft-joinnvl

display Count of one column from another table even when the count is zero

I have two tables A and B. In Table A (Oracle sql), an unique column(not primary key) code may have some records in table B.

Example:

Code "A" has 3 entries, Code "B" has 2 entries and code "C" has 0 entries in table B. I want the query to display the code and its count of records in Table B. 

A 3
B 2
C 0,

But i am not getting the code with zero records in table B, i.e C 0.

Please anyone can help me with the query.

Solution

  • GROUP BY with LEFT JOIN solution:

    select a.code,
       a.name,
       count(b.code)
from A a
  LEFT JOIN B b ON a.code = b.code
group by a.code, a.name

    Correlated sub-query solution:

    select a.code,
       a.name,
       (select count(*) from B b where a.code = b.code)
from A a

    Perhaps you need to do SELECT DISTINCT here.