February 25, 2013 at 8:34 pm
I have two table ACINF and ACPMNTA
ACINF
COMCODE ACTCODE ACTDESC
3305130000000000CURRENT ASSETS
3305130100000000Loans
3305130100010000
3305130100020000
3305130100090000Loan to Others
3305130100090001Loan to Others
3305130200000000Loan to Employee (RHEL)
3305130200010000Loan to Emplyee (RHEL)
3305130200010001Loan to Emplyee (RHEL)
3305130200010003Advance to Staffs
3305130200010004Advance to Parties
3305130200010005
3305130200010006
3305130200010007Advances to Land Owner ( Refundable )
query 1 :
SELECT COMCOD, ACTCODE, ACTDESC
FROM ACINF
WHERE (COMCOD = 3305) AND (ACTDESC = '')
Returns
COMCOD ACTCODE ACTDESC
3305130100010000
3305130100020000
3305130200020002
3305130200020004
3305130200020005
3305130200020006
3305130200020007
3305130200020008
3305130200020009
3305130200020012
3305130200020013
3305130200020014
3305130200020015
3305130200020018
3305130200020019
3305130200020022
3305130200020026
3305130200020027
3305130200020028
3305130200020038
3305130200020041
3305130200020047
3305130200020048
3305130200020056
3305130200020060
Table ACPMNTA
COMCOD VOUNUM ACTCODE
2305PV201205000001130200010001
2305PV201205000002130200010001
2305PV201205000003130200010001
2305PV201205000004130200010001
2305PV201205000005130200010001
2305PV201205000006130200010001
now i need to run a query to find which of the ACTCODE from ACINF (query 1 showed in the middle) is present in the ACPMNTA.
COMCOD = nchar(4)
ACTCODE= nchar(12)
ACTDESC=nvarchar(100)
February 25, 2013 at 10:02 pm
Take a look at inner joins.
Based on what you have written this should get you the information you require.
February 26, 2013 at 4:34 am
Is this what you're looking for?
SELECT a.COMCODE, a.ACTCODE, a.ACTDESC
FROM ACINF a
INNER JOIN ACPMNTA b ON b.ACTCODE = a.ACTCODE
WHERE a.COMCOD = 3305
AND a.ACTDESC = '';
I'm joining on ACTCODE because I read that you wanted to find the rows in ACINF where ACTCODE is present in ACPMNTA. I included the WHERE clause from your middle query, but you don't have to if it isn't needed.
February 27, 2013 at 1:09 am
Thank you very much it id working.
February 27, 2013 at 8:26 am
No problem. I'm glad it worked for you.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply