Query joining in sql server 2005
- by Domnic
I have two queries like:
SELECT PC_COMP_CODE,
PC_SL_LDGR_CODE,
PC_SL_ACNO ACCOUNT,
COUNT(PC_CHEQUE_NO) CHQS,
SUM(CONVERT(FLOAT, PC_AMOUNT)) CHQ_AMT
FROM GLAS_PDC_CHEQUES
WHERE PC_COMP_CODE = '1'
AND PC_DISCD IS NULL
GROUP BY PC_SL_LDGR_CODE,
PC_SL_ACNO ,PC_COMP_CODE
ORDER BY PC_SL_ACNO
--------------------------------------------------
SELECT COAD_PTY_FULL_NAME,PC_COMP_CODE, PC_SL_LDGR_CODE, PC_SL_ACNO, PC_DEPT_NO, PC_DOC_TYPE, PC_CHEQUE_NO, PC_BANK_AC_NO FROM GLAS_PTY_ADDRESS,GLAS_SBLGR_MASTERS,GLAS_PDC_CHEQUES WHERE
COAD_COMP_CODE = '1' AND SLMA_COMP_CODE = COAD_COMP_CODE AND SLMA_ADDR_ID = COAD_ADDR_ID
AND SLMA_LDGRCTL_CODE = PC_SL_LDGR_CODE AND PC_COMP_CODE=SLMA_COMP_CODE
AND SLMA_ACNO = PC_SL_ACNO
AND SLMA_LDGRCTL_YEAR = DBO.GLAS_VALIDATIONS_GET_OPEN_YEAR(PC_COMP_CODE)
If I execute first query alone I get 5 records...
If I join the above two query like:
SELECT
PC_COMP_CODE,
PC_SL_LDGR_CODE,
PC_SL_ACNO ACCOUNT,
COUNT(PC_CHEQUE_NO) CHQS,
SUM(CONVERT(FLOAT, PC_AMOUNT)) CHQ_AMT,
COAD_PTY_FULL_NAME
FROM GLAS_PDC_CHEQUES
LEFT OUTER JOIN GLAS_SBLGR_MASTERS
ON( SLMA_COMP_CODE=PC_COMP_CODE AND
SLMA_LDGRCTL_CODE = PC_SL_LDGR_CODE AND SLMA_ACNO = PC_SL_ACNO )
LEFT OUTER JOIN GLAS_PTY_ADDRESS ON( SLMA_COMP_CODE = COAD_COMP_CODE AND SLMA_ADDR_ID = COAD_ADDR_ID)
WHERE PC_COMP_CODE = '1'
AND PC_DISCD IS NULL AND SLMA_LDGRCTL_YEAR = DBO.GLAS_VALIDATIONS_GET_OPEN_YEAR(PC_COMP_CODE)
GROUP BY PC_SL_LDGR_CODE,
PC_SL_ACNO ,PC_COMP_CODE,COAD_PTY_FULL_NAME
ORDER BY PC_SL_ACNO
then I just get 2 records....
I need that 5 records to display after join.....
How can I do it?
