Get the count of records from another table

I have a view that looks like this:

which is defined using this SQL statement:

SELECT
    p.p_ccode AS C_CODE,
    p.p_pcode AS P_CODE,
    p.p_phcode AS PH_CODE,
    PO.m_doc_type AS TYPE,
    PO.crtime AS DATE
FROM 
    TABLE1 AS p
LEFT OUTER JOIN 
    DOCTABLE AS PO ON PO.m_ccode = p.p_ccode
                   AND PO.m_pcode = p.p_pcode
                   AND PO.m_phcode = p.p_phcode

Now I want to count the records for another table (TABLE 2) based on the C_CODE, P_CODE and PH_CODE and display it as a column TOTAL in my view.

How can I do this?

Answer

one way is to use subquery:

SELECT
    p.p_ccode AS C_CODE,
    p.p_pcode AS P_CODE,
    p.p_phcode AS PH_CODE,
    PO.m_doc_type AS TYPE,
    PO.crtime AS DATE,
    (select count(*) from tale2 t2 where t2.C_CODE = p.p_ccode and t2.P_CODE = p.P_CODE and t2.PH_CODE = p.PH_CODE) as total
FROM TABLE1 AS p
LEFT OUTER JOIN DOCTABLE AS PO 
    ON PO.m_ccode = p.p_ccode
    AND PO.m_pcode = p.p_pcode
    AND PO.m_phcode = p.p_phcode