Query joining in sql server 2005
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
开发者_Go百科 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?
I grepped all the fields in the first query to make it more readable and more likely that someone can make sense of things. Unfortunately, I gave up at that point.
Results below, but for what its worth, I'll take a shot in the dark and say that you want to join with a UNION.
SELECT id, first_name, account, COUNT(pants) pants, SUM(CONVERT(FLOAT, cost)) amount
FROM table1
WHERE id = '1' AND discount IS NULL GROUP BY first_name, account ,id
ORDER BY account
SELECT full_name,id, first_name, account, department, coffee_pref, pants, acct_no
FROM table2,table3,table1
WHERE secret_code = '1'
AND public_code = secret_code
AND addr_id = phone_id
AND nickname = first_name
AND id=public_code
AND movie_rating = account
AND album_year = DBO.get_random_year(id)
精彩评论