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