Query joining in sql server 2005

Posted by Domnic on Stack Overflow See other posts from Stack Overflow or by Domnic
Published on 2010-01-25T06:25:33Z Indexed on 2010/04/10 15:33 UTC
Read the original article Hit count: 293

Filed under:

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?

© Stack Overflow or respective owner

Related posts about sql-server