Home » SQL & PL/SQL » SQL & PL/SQL » see my query and give me right idea
see my query and give me right idea [message #187198] Fri, 11 August 2006 04:56 Go to next message
mfa786
Messages: 210
Registered: February 2006
Location: karachi
Senior Member
sir see my query mfa2 is mastr and mfa3 is detail this query give me union type result and mfa2*mfa3 but i need master detail result

SELECT mfa3.stdcode,mfa2.vhno,mfa3.name FROM (
SELECT STUSECMTAMT.stdcode,STUSECMTAMT.sem_code,COURSES.name,COURSES.id,COURSES.crhrs,STUDEG.DEGREE_code,SCHEME_DEG_DET.amount FROM STUSECMTAMT,STUDEG,SCHEME_DEG_DET,SECCORS,COURSES,STUSEC
WHERE STUSECMTAMT.STUDEGid=STUDEG.STUDEGid
AND
STUSECMTAMT.scheme_mst_id=SCHEME_DEG_DET.scheme_mst_id AND
STUDEG.degree_code= SCHEME_DEG_DET.degree_code
AND
STUSEC.seccorscode=SECCORS.seccorscode AND SECCORS.coursescode=COURSES.coursescode AND STUSEC.vhno=STUSECMTAMT.VHNO
AND STUSECMTAMT.stdcode ='021-03-12966') mfa3,
(SELECT STUSECMTAMT.stdcode,STUSECMTAMT.vhno,
MAX(DECODE(RATES.description,'Admission',STUINV.amt)) Admission,
MAX(DECODE(RATES.description,'Registration',STUINV.amt)) Registration,
MAX(DECODE(RATES.description,'Student Council',STUINV.amt)) S_Council,
MAX(DECODE(RATES.description,'Caution Money',STUINV.amt)) C_Money,
MAX(DECODE(RATES.description,'Misc',STUINV.amt)) Misc,
MAX(DECODE(RATES.description,'Toefl',STUINV.amt)) Toefl
FROM STUSECMTAMT,STUINV,RATES,STUJOURNAL,STUDEG WHERE STUSECMTAMT.vhno=STUJOURNAL.ref_no AND STUSECMTAMT.VHNO=STUINV.vhno AND STUSECMTAMT.STUDEGid=STUDEG.STUDEGid AND STUINV.ratescode=RATES.ratescode AND STUSECMTAMT.SCHEME_MST_ID=RATES.SCHEME_MST_ID AND STUSECMTAMT.stdcode ='021-03-12966'
GROUP BY STUSECMTAMT.stdcode,STUSECMTAMT.vhno
) mfa2 WHERE mfa3.stdcode=mfa2.stdcode


pleae give me idea how i get right result
thanks
aamir
Re: see my query and give me right idea [message #187243 is a reply to message #187198] Fri, 11 August 2006 08:09 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Please be considerate and format please.
Previous Topic: remove duplicate records
Next Topic: Read only dropdown?
Goto Forum:
  


Current Time: Sun Dec 11 06:31:41 CST 2016

Total time taken to generate the page: 0.12797 seconds