.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

query joining

Posted By: Dom     Posted Date: January 24, 2010    Points:2   Category :Sql Server
AS i have 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
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 got 5 records...
1 01 A00007 4 15126
1 01 A00031 2 24200
1 01 A00032 6 27600
1 01 A00041 6 234000
1 01 A00047 22 134000
1 02 S00053 1 200


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 got only 2 records....
1 01 A00007 4 15126 AIRPORT VIDEO
1 02 S00053 1 200 DEMO3

I need that 5 records to display after join.....
how can i?

Responses
Author: Lakhan Pal Garg             
Posted Date: April 21, 2010     Points: 5   

What you can do is Create two table variables and add the result of these two queries into seperate table variable and then can use left outeroin to get the Required Result.

Thanks & Regards
Lakhan Pal Garg
http://lakhangarg.blogspot.com



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend