lundi 16 mars 2015

Multiple joins on same table?

I have two table:


tbl_EmploymentSegmentEM:



╔══════╦═════════════╦════════════╦═══════════════╦═══════════════════════════════╦════════════╦═════════════╦══════════════════════════╦════════════════╗
║ SrNo ║ CIBILTuefID ║ Prospectno ║ ApplicantType ║ ApplicantName ║ SegmentTag ║ AccountType ║ DateReportedandCertified ║ OccupationCode ║
╠══════╬═════════════╬════════════╬═══════════════╬═══════════════════════════════╬════════════╬═════════════╬══════════════════════════╬════════════════╣
║ 1 ║ 1 ║ 718580 ║ APPLICANT ║ RAJKUMAR GIRISHCHANDRA PANDEY ║ E01 ║ 10 ║ 31122014 ║ 02 ║
║ 2 ║ 4 ║ 718638 ║ APPLICANT ║ Anil Kumar Aggarwal ║ E01 ║ 10 ║ 31122014 ║ 01 ║
╚══════╩═════════════╩════════════╩═══════════════╩═══════════════════════════════╩════════════╩═════════════╩══════════════════════════╩════════════════╝


tbl_CIBILFieldDescription:



╔════════╦══════════╦══════════════════════════════╦═══════╦═════════════════════════════╗
║ Header ║ FieldTag ║ FieldName ║ Value ║ ValueDescription ║
╠════════╬══════════╬══════════════════════════════╬═══════╬═════════════════════════════╣
║ PT ║ 03 ║ TelephoneType ║ 03 ║ Office Phone ║
║ EM ║ 03 ║ OccupationCode ║ 01 ║ Salaried ║
║ EM ║ 03 ║ OccupationCode ║ 02 ║ Self Employed Professional. ║
║ EM ║ 03 ║ OccupationCode ║ 03 ║ Self Employed ║
║ EM ║ 03 ║ OccupationCode ║ 04 ║ Others ║
║ EM ║ 05 ║ NetGrossIncomeIndicator ║ G ║ Gross Income ║
║ EM ║ 05 ║ NetGrossIncomeIndicator ║ N ║ Net Income ║
║ EM ║ 06 ║ MonthlyAnnualIncomeIndicator ║ M ║ Net Monthly ║
║ EM ║ 06 ║ MonthlyAnnualIncomeIndicator ║ A ║ Net Annual ║
║ SC ║ 01 ║ ScoreCardName ║ 01 ║ CIBILTUSCR ║
╚════════╩══════════╩══════════════════════════════╩═══════╩═════════════════════════════╝


I am trying to get Account Type and Occupation code description from tbl_CIBILFieldDescription for the corresponding values.


I tried this :



SELECT DISTINCT CIBILTuefID,
Prospectno,
ApplicantType,
ApplicantName,
SegmentTag,
AccountType,
DateReportedandCertified,
OccupationCode,
mst.ValueDescription AS OccupationCodeDesc,
Income,
NetGrossIncomeIndicator,
mst.ValueDescription AS NetGrossIncomeIndicatorDesc,
MonthlyAnnualIncomeIndicator,
DateofEntryforErrorCode,
ErrorCode,
DateofEntryforCIBILRemarksCode,
CIBILRemarksCode,
DateofEntryforErrorDisputeRemarksCode,
ErrorDisputeRemarksCode1,
ErrorDisputeRemarksCode2,
MkrId,
MkdDt
FROM tbl_EmploymentSegmentEM EM
INNER JOIN tbl_CIBILFieldDescription mst
ON 1 = 1

WHERE mst.Header = 'EM'
AND mst.FieldName = 'OccupationCode'
AND mst.Value = EM.OccupationCode


And it seems to work ok for OccupationCode but what if I want both OccupationCode and AccountType from the same query? What is the best way to do this?


Aucun commentaire:

Enregistrer un commentaire