Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a Table Enterpries with Primary Key 'RCNO'. All other tables have a one to many relationship with Enterprise via it's RCNO column. I make a Join of all the tables and i get nothing. Below is the query. I would like to attach the tables(sql). I can't find a place to upload.

SELECT
 enterprise.`RCNO` AS enterprise_RCNO,
 enterprise.`ADDRESS` AS enterprise_ADDRESS,
 enterprise.`NAME` AS enterprise_NAME,
 enterprise.`WEBSITE` AS enterprise_WEBSITE,
 enterpriseinfo.`ACCTYEAR` AS enterpriseinfo_ACCTYEAR,
 enterpriseinfo.`BASISFORCOMPLETION` AS enterpriseinfo_BASISFORCOMPLETION,
 enterpriseinfo.`HASEQUITYINVESTMENTABROAD` AS enterpriseinfo_HASEQUITYINVESTMENTABROAD,
 enterpriseinfo.`ISPARTOFGROUP` AS enterpriseinfo_ISPARTOFGROUP,
 enterpriseinfo.`NOOFSTAFF` AS enterpriseinfo_NOOFSTAFF,
 foreignenterprises.`ENTERPRISENAME` AS foreignenterprises_ENTERPRISENAME,
 nigerianenterprises.`ENTERPRISENAME` AS nigerianenterprises_ENTERPRISENAME,
 principalactivityturnover.`ACCTYEAR` AS principalactivityturnover_ACCTYEAR,
 principalactivityturnover.`AREAOFACTIVITY` AS principalactivityturnover_AREAOFACTIVITY,
 principalactivityturnover.`PERCENTAGECONTRIBUTION` AS principalactivityturnover_PERCENTAGECONTRIBUTION,
 bookvalueofshareholderscapital.`ACCTYEAR` AS bookvalueofshareholderscapital_ACCTYEAR,
 bookvalueofshareholderscapital.`BOOKVALUE` AS bookvalueofshareholderscapital_BOOKVALUE,
 bookvalueofshareholderscapital.`COUNTRY` AS bookvalueofshareholderscapital_COUNTRY,
 bookvalueofshareholderscapital.`NAME` AS bookvalueofshareholderscapital_NAME,
 bookvalueofshareholderscapital.`PERCENTAGEOWNERSHIP` AS bookvalueofshareholderscapital_PERCENTAGEOWNERSHIP,
 bookvalueofshareholderscapital.`REVALUATION` AS bookvalueofshareholderscapital_REVALUATION,
 bookvalueofshareholderscapital.`SHARESPURCHASED` AS bookvalueofshareholderscapital_SHARESPURCHASED,
 bookvalueofshareholderscapital.`SHARESSOLD` AS bookvalueofshareholderscapital_SHARESSOLD,
 inwarddirectinvestment.`ACCTYEAR` AS inwarddirectinvestment_ACCTYEAR,
 inwarddirectinvestment.`BOOKSHARECAPITAL` AS inwarddirectinvestment_BOOKSHARECAPITAL,
 inwarddirectinvestment.`COUNTRY` AS inwarddirectinvestment_COUNTRY,
 inwarddirectinvestment.`NAME` AS inwarddirectinvestment_NAME,
 inwarddirectinvestment.`PURCHASEDSHAREFROMINVESTOR` AS inwarddirectinvestment_PURCHASEDSHAREFROMINVESTOR,
 inwarddirectinvestment.`SALESOFSHARETOINVESTOR` AS inwarddirectinvestment_SALESOFSHARETOINVESTOR,
 inwarddirectinvestment.`SHARECAPITALPERCENTAGE` AS inwarddirectinvestment_SHARECAPITALPERCENTAGE,
 inwarddirectinvestmentsubsidiary.`ACCTYEAR` AS inwarddirectinvestmentsubsidiary_ACCTYEAR,
 inwarddirectinvestmentsubsidiary.`BOOKSHARECAPITAL` AS inwarddirectinvestmentsubsidiary_BOOKSHARECAPITAL,
 inwarddirectinvestmentsubsidiary.`COUNTRY` AS inwarddirectinvestmentsubsidiary_COUNTRY,
 inwarddirectinvestmentsubsidiary.`NAME` AS inwarddirectinvestmentsubsidiary_NAME,
 inwarddirectinvestmentsubsidiary.`PURCHASEDSHAREFROMINVESTOR` AS inwarddirectinvestmentsubsidiary_PURCHASEDSHAREFROMINVESTOR,
 inwarddirectinvestmentsubsidiary.`SALESOFSHARETOINVESTOR` AS inwarddirectinvestmentsubsidiary_SALESOFSHARETOINVESTOR,
 inwarddirectinvestmentsubsidiary.`SHARECAPITALPERCENTAGE` AS inwarddirectinvestmentsubsidiary_SHARECAPITALPERCENTAGE,
 inwarddirectinvestmentsubsidiaryp.`ACCTYEAR` AS inwarddirectinvestmentsubsidiaryp_ACCTYEAR,
 inwarddirectinvestmentsubsidiaryp.`BOOKSHARECAPITAL` AS inwarddirectinvestmentsubsidiaryp_BOOKSHARECAPITAL,
 inwarddirectinvestmentsubsidiaryp.`COUNTRY` AS inwarddirectinvestmentsubsidiaryp_COUNTRY,
 inwarddirectinvestmentsubsidiaryp.`NAME` AS inwarddirectinvestmentsubsidiaryp_NAME,
 inwarddirectinvestmentsubsidiaryp.`PURCHASEDSHAREFROMINVESTOR` AS inwarddirectinvestmentsubsidiaryp_PURCHASEDSHAREFROMINVESTOR,
 inwarddirectinvestmentsubsidiaryp.`SALESOFSHARETOINVESTOR` AS inwarddirectinvestmentsubsidiaryp_SALESOFSHARETOINVESTOR,
 inwarddirectinvestmentsubsidiaryp.`SHARECAPITALPERCENTAGE` AS inwarddirectinvestmentsubsidiaryp_SHARECAPITALPERCENTAGE,
 inwardportfolioinvestmentsubsidiary.`ACCTYEAR` AS inwardportfolioinvestmentsubsidiary_ACCTYEAR,
 inwardportfolioinvestmentsubsidiary.`COUNTRY` AS inwardportfolioinvestmentsubsidiary_COUNTRY,
 inwardportfolioinvestmentsubsidiary.`MARKETVALUE` AS inwardportfolioinvestmentsubsidiary_MARKETVALUE,
 inwardportfolioinvestmentsubsidiary.`NAME` AS inwardportfolioinvestmentsubsidiary_NAME,
 inwardportfolioinvestmentsubsidiary.`PURCHASEOFSHARES` AS inwardportfolioinvestmentsubsidiary_PURCHASEOFSHARES,
 inwardportfolioinvestmentsubsidiary.`SALESOFSHARES` AS inwardportfolioinvestmentsubsidiary_SALESOFSHARES,
 inwardportfolioinvestmentsubsidiary.`SHARECAPITALPERCENTAGE` AS inwardportfolioinvestmentsubsidiary_SHARECAPITALPERCENTAGE,
 inwardportfolioinvestmentsubsidiaryp.`ACCTYEAR` AS inwardportfolioinvestmentsubsidiaryp_ACCTYEAR,
 inwardportfolioinvestmentsubsidiaryp.`COUNTRY` AS inwardportfolioinvestmentsubsidiaryp_COUNTRY,
 inwardportfolioinvestmentsubsidiaryp.`MARKETVALUE` AS inwardportfolioinvestmentsubsidiaryp_MARKETVALUE,
 inwardportfolioinvestmentsubsidiaryp.`NAME` AS inwardportfolioinvestmentsubsidiaryp_NAME,
 inwardportfolioinvestmentsubsidiaryp.`PURCHASEOFSHARES` AS inwardportfolioinvestmentsubsidiaryp_PURCHASEOFSHARES,
 inwardportfolioinvestmentsubsidiaryp.`SALESOFSHARES` AS inwardportfolioinvestmentsubsidiaryp_SALESOFSHARES,
 inwardportfolioinvestmentsubsidiaryp.`SHARECAPITALPERCENTAGE` AS inwardportfolioinvestmentsubsidiaryp_SHARECAPITALPERCENTAGE,
 externaldebtnonresident.`ACCTYEAR` AS externaldebtnonresident_ACCTYEAR,
 externaldebtnonresident.`INTERESTPAYABLE` AS externaldebtnonresident_INTERESTPAYABLE,
 externaldebtnonresident.`NETTRASACTIONS` AS externaldebtnonresident_NETTRASACTIONS,
 externaldebtnonresident.`OTHERCHARGES` AS externaldebtnonresident_OTHERCHARGES,
 externaldebtnonresident.`STOCKOFDEBT` AS externaldebtnonresident_STOCKOFDEBT,
 externaldebtnonresidentsubsidiary.`ACCTYEAR` AS externaldebtnonresidentsubsidiary_ACCTYEAR,
 externaldebtnonresidentsubsidiary.`INTERESTPAYABLE` AS externaldebtnonresidentsubsidiary_INTERESTPAYABLE,
 externaldebtnonresidentsubsidiary.`NETTRASACTIONS` AS externaldebtnonresidentsubsidiary_NETTRASACTIONS,
 externaldebtnonresidentsubsidiary.`OTHERCHARGES` AS externaldebtnonresidentsubsidiary_OTHERCHARGES,
 externaldebtnonresidentsubsidiary.`STOCKOFDEBT` AS externaldebtnonresidentsubsidiary_STOCKOFDEBT,
 externaldebtnonresidentsubsidiaryp.`ACCTYEAR` AS externaldebtnonresidentsubsidiaryp_ACCTYEAR,
 externaldebtnonresidentsubsidiaryp.`INTERESTPAYABLE` AS externaldebtnonresidentsubsidiaryp_INTERESTPAYABLE,
 externaldebtnonresidentsubsidiaryp.`NETTRASACTIONS` AS externaldebtnonresidentsubsidiaryp_NETTRASACTIONS,
 externaldebtnonresidentsubsidiaryp.`OTHERCHARGES` AS externaldebtnonresidentsubsidiaryp_OTHERCHARGES,
 externaldebtnonresidentsubsidiaryp.`STOCKOFDEBT` AS externaldebtnonresidentsubsidiaryp_STOCKOFDEBT,
 externaldebtunrelated.`ACCTYEAR` AS externaldebtunrelated_ACCTYEAR,
 externaldebtunrelated.`CLOSINGBALANCE` AS externaldebtunrelated_CLOSINGBALANCE,
 externaldebtunrelated.`DEBTINSTRUMENT` AS externaldebtunrelated_DEBTINSTRUMENT,
 externaldebtunrelated.`RECIEPTS` AS externaldebtunrelated_RECIEPTS,
 externaldebtunrelated.`REPAYMENTS` AS externaldebtunrelated_REPAYMENTS,
 externaldebtunrelated.`REVALUATIONS` AS externaldebtunrelated_REVALUATIONS,
 externaldebtunrelated.`INTERESTPAYABLE` AS externaldebtunrelated_INTERESTPAYABLE,
 externaldebtliabilitiescountry.`ACCTYEAR` AS externaldebtliabilitiescountry_ACCTYEAR,
 externaldebtliabilitiescountry.`COUNTRY` AS externaldebtliabilitiescountry_COUNTRY,
 externaldebtliabilitiescountry.`DEBTAMOUNT` AS externaldebtliabilitiescountry_DEBTAMOUNT,
 externaldebtliabilitiescountry.`DEBTINSTRUMENT` AS externaldebtliabilitiescountry_DEBTINSTRUMENT,
 otherincomefromnonresidents.`ACCTYEAR` AS otherincomefromnonresidents_ACCTYEAR,
 otherincomefromnonresidents.`INCOMEITEM` AS otherincomefromnonresidents_INCOMEITEM,
 otherincomefromnonresidents.`INCOMEVALUE` AS otherincomefromnonresidents_INCOMEVALUE,
 shareholderscapitalofsubsidiary.`ACCTYEAR` AS shareholderscapitalofsubsidiary_ACCTYEAR,
 shareholderscapitalofsubsidiary.`BOOKVALUEOFSHAREHOLDERS` AS shareholderscapitalofsubsidiary_BOOKVALUEOFSHAREHOLDERS,
 shareholderscapitalofsubsidiary.`CHANGEINPAIDUPSHARECAPITAL` AS shareholderscapitalofsubsidiary_CHANGEINPAIDUPSHARECAPITAL,
 shareholderscapitalofsubsidiary.`CHANGEINRETAINEDEARNING` AS shareholderscapitalofsubsidiary_CHANGEINRETAINEDEARNING,
 shareholderscapitalofsubsidiary.`COUNTRY` AS shareholderscapitalofsubsidiary_COUNTRY,
 shareholderscapitalofsubsidiary.`NAME` AS shareholderscapitalofsubsidiary_NAME,
 shareholderscapitalofsubsidiary.`REVALUATION` AS shareholderscapitalofsubsidiary_REVALUATION,
 enterpriseinvestments.`ACCTYEAR` AS enterpriseinvestments_ACCTYEAR,
 enterpriseinvestments.`INVESTMENTAMOUNT` AS enterpriseinvestments_INVESTMENTAMOUNT,
 enterpriseinvestments.`INVESTMENTITEM` AS enterpriseinvestments_INVESTMENTITEM,
 subsidiaryincomeaccount.`ACCTYEAR` AS subsidiaryincomeaccount_ACCTYEAR,
 subsidiaryincomeaccount.`DIVIDENSANDPROFITSPAID` AS subsidiaryincomeaccount_DIVIDENSANDPROFITSPAID,
 subsidiaryincomeaccount.`ENTERPRISENAME` AS subsidiaryincomeaccount_ENTERPRISENAME,
 subsidiaryincomeaccount.`NETINCOMEAFTERTAX` AS subsidiaryincomeaccount_NETINCOMEAFTERTAX,
 subsidiaryincomeaccount.`RETAINEDEARNING` AS subsidiaryincomeaccount_RETAINEDEARNING,
 equityclaimsofparent.`COUNTRY` AS equityclaimsofparent_COUNTRY,
 equityclaimsofparent.`EQUITYVALUEHELD` AS equityclaimsofparent_EQUITYVALUEHELD,
 equityclaimsofparent.`NAME` AS equityclaimsofparent_NAME,
 equityclaimsofparent.`NETPURCHASES` AS equityclaimsofparent_NETPURCHASES,
 equityclaimsofparent.`NETSALES` AS equityclaimsofparent_NETSALES,
 equityclaimsofparent.`OTHERCHARGESREVALUATION` AS equityclaimsofparent_OTHERCHARGESREVALUATION,
 equityclaimsofparent.`PERCENTAGESHARES` AS equityclaimsofparent_PERCENTAGESHARES,
 equityclaimsofparent.`ACCTYEAR` AS equityclaimsofparent_ACCTYEAR,
 equityclaimsofparentsub.`COUNTRY` AS equityclaimsofparentsub_COUNTRY,
 equityclaimsofparentsub.`EQUITYVALUEHELD` AS equityclaimsofparentsub_EQUITYVALUEHELD,
 equityclaimsofparentsub.`NAME` AS equityclaimsofparentsub_NAME,
 equityclaimsofparentsub.`NETPURCHASES` AS equityclaimsofparentsub_NETPURCHASES,
 equityclaimsofparentsub.`NETSALES` AS equityclaimsofparentsub_NETSALES,
 equityclaimsofparentsub.`OTHERCHARGESREVALUATION` AS equityclaimsofparentsub_OTHERCHARGESREVALUATION,
 equityclaimsofparentsub.`PERCENTAGESHARES` AS equityclaimsofparentsub_PERCENTAGESHARES,
 equityclaimsofparentsub.`ACCTYEAR` AS equityclaimsofparentsub_ACCTYEAR,
 equityclaimsofparentsubp.`COUNTRY` AS equityclaimsofparentsubp_COUNTRY,
 equityclaimsofparentsubp.`EQUITYVALUEHELD` AS equityclaimsofparentsubp_EQUITYVALUEHELD,
 equityclaimsofparentsubp.`NAME` AS equityclaimsofparentsubp_NAME,
 equityclaimsofparentsubp.`NETPURCHASES` AS equityclaimsofparentsubp_NETPURCHASES,
 equityclaimsofparentsubp.`NETSALES` AS equityclaimsofparentsubp_NETSALES,
 equityclaimsofparentsubp.`OTHERCHARGESREVALUATION` AS equityclaimsofparentsubp_OTHERCHARGESREVALUATION,
 equityclaimsofparentsubp.`PERCENTAGESHARES` AS equityclaimsofparentsubp_PERCENTAGESHARES,
 equityclaimsofparentsubp.`ACCTYEAR` AS equityclaimsofparentsubp_ACCTYEAR,
 externallendingtosubsidiary.`ACCTYEAR` AS externallendingtosubsidiary_ACCTYEAR,
 externallendingtosubsidiary.`AGGOFEXTERNALLENDING` AS externallendingtosubsidiary_AGGOFEXTERNALLENDING,
 externallendingtosubsidiary.`ENTITYNAME` AS externallendingtosubsidiary_ENTITYNAME,
 externallendingtosubsidiary.`INTERESTRECIEVABLE` AS externallendingtosubsidiary_INTERESTRECIEVABLE,
 externallendingtosubsidiary.`NETLENDING` AS externallendingtosubsidiary_NETLENDING,
 externallendingtosubsidiary.`OTHERCHANGESANDREVALUATION` AS externallendingtosubsidiary_OTHERCHANGESANDREVALUATION,
 externallendingrelatedparent.`ACCTYEAR` AS externallendingrelatedparent_ACCTYEAR,
 externallendingrelatedparent.`AGGEXTERNALLENDING` AS externallendingrelatedparent_AGGEXTERNALLENDING,
 externallendingrelatedparent.`INTERESTRECIEVABLE` AS externallendingrelatedparent_INTERESTRECIEVABLE,
 externallendingrelatedparent.`NETLENDING` AS externallendingrelatedparent_NETLENDING,
 externallendingrelatedparent.`REVALUATIONS` AS externallendingrelatedparent_REVALUATIONS,
 externallendingrelatedsubparent.`ACCTYEAR` AS externallendingrelatedsubparent_ACCTYEAR,
 externallendingrelatedsubparent.`AGGEXTERNALLENDING` AS externallendingrelatedsubparent_AGGEXTERNALLENDING,
 externallendingrelatedsubparent.`INTERESTRECIEVABLE` AS externallendingrelatedsubparent_INTERESTRECIEVABLE,
 externallendingrelatedsubparent.`NETLENDING` AS externallendingrelatedsubparent_NETLENDING,
 externallendingrelatedsubparent.`REVALUATIONS` AS externallendingrelatedsubparent_REVALUATIONS,
 debtinstrumentsheldunrelated.`ACCTYEAR` AS debtinstrumentsheldunrelated_ACCTYEAR,
 debtinstrumentsheldunrelated.`CLOSINGBALANCE` AS debtinstrumentsheldunrelated_CLOSINGBALANCE,
 debtinstrumentsheldunrelated.`DEBTINSTRUMENT` AS debtinstrumentsheldunrelated_DEBTINSTRUMENT,
 debtinstrumentsheldunrelated.`INTERESTRECIEVABLE` AS debtinstrumentsheldunrelated_INTERESTRECIEVABLE,
 debtinstrumentsheldunrelated.`OPENINGBALANCE` AS debtinstrumentsheldunrelated_OPENINGBALANCE,
 debtinstrumentsheldunrelated.`PRINCIPALREPAYMENT` AS debtinstrumentsheldunrelated_PRINCIPALREPAYMENT,
 debtinstrumentsheldunrelated.`PURCHASE` AS debtinstrumentsheldunrelated_PURCHASE,
 debtinstrumentsheldunrelated.`REVALUATION` AS debtinstrumentsheldunrelated_REVALUATION,
 debtinstrumenturcountry.`ACCTYEAR` AS debtinstrumenturcountry_ACCTYEAR,
 debtinstrumenturcountry.`DEBTAMOUNT` AS debtinstrumenturcountry_DEBTAMOUNT,
 debtinstrumenturcountry.`DEBTINSTRUMENT` AS debtinstrumenturcountry_DEBTINSTRUMENT,
 debtinstrumenturcountry.`DEBTORCOUNTRY` AS debtinstrumenturcountry_DEBTORCOUNTRY,
 otherexpenditurenr.`ACCTYEAR` AS otherexpenditurenr_ACCTYEAR,
 otherexpenditurenr.`EXPENSEITEM` AS otherexpenditurenr_EXPENSEITEM,
 otherexpenditurenr.`EXPENSEVALUE` AS otherexpenditurenr_EXPENSEVALUE,
 depositwithnonresidentbanks.`ACCTYEAR` AS depositwithnonresidentbanks_ACCTYEAR,
 depositwithnonresidentbanks.`BANKNAME` AS depositwithnonresidentbanks_BANKNAME,
 depositwithnonresidentbanks.`CHANGESDUETOEXCHANGE` AS depositwithnonresidentbanks_CHANGESDUETOEXCHANGE,
 depositwithnonresidentbanks.`CURRENTOUTSTANDINGBALANCE` AS depositwithnonresidentbanks_CURRENTOUTSTANDINGBALANCE,
 depositwithnonresidentbanks.`TRANSACTIONSTHISYEAR` AS depositwithnonresidentbanks_TRANSACTIONSTHISYEAR,
 finderivativecontractwithnr.`ACCTYEAR` AS finderivativecontractwithnr_ACCTYEAR,
 finderivativecontractwithnr.`CASHRECIEPTSANDDERIVATIVEPAYMENT` AS finderivativecontractwithnr_CASHRECIEPTSANDDERIVATIVEPAYMENT,
 finderivativecontractwithnr.`NONRESIDENTNAME` AS finderivativecontractwithnr_NONRESIDENTNAME,
 finderivativecontractwithnr.`OUTSTANDINGMARKETPOSITION` AS finderivativecontractwithnr_OUTSTANDINGMARKETPOSITION,
 foriegngovtdebtsecuritycountry.`ACCTYEAR` AS foriegngovtdebtsecuritycountry_ACCTYEAR,
 foriegngovtdebtsecuritycountry.`CHANGESINVALUEDUETOEXCHANGE` AS foriegngovtdebtsecuritycountry_CHANGESINVALUEDUETOEXCHANGE,
 foriegngovtdebtsecuritycountry.`INTERESTRECIEVABLE` AS foriegngovtdebtsecuritycountry_INTERESTRECIEVABLE,
 foriegngovtdebtsecuritycountry.`NAME` AS foriegngovtdebtsecuritycountry_NAME,
 foriegngovtdebtsecuritycountry.`OTHERCHANGESINCLUDINGREVALUATION` AS foriegngovtdebtsecuritycountry_OTHERCHANGESINCLUDINGREVALUATION,
 foriegngovtdebtsecuritycountry.`OUTSTANDINGMARKETVALUE` AS foriegngovtdebtsecuritycountry_OUTSTANDINGMARKETVALUE,
dividendearningsprofit.`ACCTYEAR` AS dividendearningsprofit_ACCTYEAR,
     dividendearningsprofit.`DIVIDENDSPAID` AS dividendearningsprofit_DIVIDENDSPAID,
     dividendearningsprofit.`INTERESTPAID` AS dividendearningsprofit_INTERESTPAID,
     dividendearningsprofit.`INTERESTRECIEVED` AS dividendearningsprofit_INTERESTRECIEVED,
     dividendearningsprofit.`NETINCOMEAFTERTAX` AS dividendearningsprofit_NETINCOMEAFTERTAX,
     dividendearningsprofit.`REALISEDHOLDINGS` AS dividendearningsprofit_REALISEDHOLDINGS,
     dividendearningsprofit.`RETAINEDEARNING` AS dividendearningsprofit_RETAINEDEARNING
FROM
 `enterprise` enterprise INNER JOIN `enterpriseinfo` enterpriseinfo ON enterprise.`RCNO` = enterpriseinfo.`RCNO`
 INNER JOIN `foreignenterprises` foreignenterprises ON enterprise.`RCNO` = foreignenterprises.`RCNO`
 INNER JOIN `nigerianenterprises` nigerianenterprises ON enterprise.`RCNO` = nigerianenterprises.`RCNO`
 INNER JOIN `principalactivityturnover` principalactivityturnover ON enterprise.`RCNO` = principalactivityturnover.`RCNO`
 INNER JOIN `bookvalueofshareholderscapital` bookvalueofshareholderscapital ON enterprise.`RCNO` = bookvalueofshareholderscapital.`RCNO`
 INNER JOIN `inwarddirectinvestment` inwarddirectinvestment ON enterprise.`RCNO` = inwarddirectinvestment.`RCNO`
 INNER JOIN `inwarddirectinvestmentsubsidiary` inwarddirectinvestmentsubsidiary ON enterprise.`RCNO` = inwarddirectinvestmentsubsidiary.`RCNO`
 INNER JOIN `inwarddirectinvestmentsubsidiaryp` inwarddirectinvestmentsubsidiaryp ON enterprise.`RCNO` = inwarddirectinvestmentsubsidiaryp.`RCNO`
 INNER JOIN `inwardportfolioinvestmentsubsidiary` inwardportfolioinvestmentsubsidiary ON enterprise.`RCNO` = inwardportfolioinvestmentsubsidiary.`RCNO`
 INNER JOIN `inwardportfolioinvestmentsubsidiaryp` inwardportfolioinvestmentsubsidiaryp ON enterprise.`RCNO` = inwardportfolioinvestmentsubsidiaryp.`RCNO`
 INNER JOIN `externaldebtnonresident` externaldebtnonresident ON enterprise.`RCNO` = externaldebtnonresident.`RCNO`
 INNER JOIN `externaldebtnonresidentsubsidiary` externaldebtnonresidentsubsidiary ON enterprise.`RCNO` = externaldebtnonresidentsubsidiary.`RCNO`
 INNER JOIN `externaldebtnonresidentsubsidiaryp` externaldebtnonresidentsubsidiaryp ON enterprise.`RCNO` = externaldebtnonresidentsubsidiaryp.`RCNO`
 INNER JOIN `externaldebtunrelated` externaldebtunrelated ON enterprise.`RCNO` = externaldebtunrelated.`RCNO`
 INNER JOIN `externaldebtliabilitiescountry` externaldebtliabilitiescountry ON enterprise.`RCNO` = externaldebtliabilitiescountry.`RCNO`
 INNER JOIN `otherincomefromnonresidents` otherincomefromnonresidents ON enterprise.`RCNO` = otherincomefromnonresidents.`RCNO`
 INNER JOIN `shareholderscapitalofsubsidiary` shareholderscapitalofsubsidiary ON enterprise.`RCNO` = shareholderscapitalofsubsidiary.`RCNO`
 INNER JOIN `enterpriseinvestments` enterpriseinvestments ON enterprise.`RCNO` = enterpriseinvestments.`RCNO`
 INNER JOIN `subsidiaryincomeaccount` subsidiaryincomeaccount ON enterprise.`RCNO` = subsidiaryincomeaccount.`RCNO`
 INNER JOIN `equityclaimsofparent` equityclaimsofparent ON enterprise.`RCNO` = equityclaimsofparent.`RCNO`
 INNER JOIN `equityclaimsofparentsub` equityclaimsofparentsub ON enterprise.`RCNO` = equityclaimsofparentsub.`RCNO`
 INNER JOIN `equityclaimsofparentsubp` equityclaimsofparentsubp ON enterprise.`RCNO` = equityclaimsofparentsubp.`RCNO`
 INNER JOIN `externallendingtosubsidiary` externallendingtosubsidiary ON enterprise.`RCNO` = externallendingtosubsidiary.`RCNO`
 INNER JOIN `externallendingrelatedparent` externallendingrelatedparent ON enterprise.`RCNO` = externallendingrelatedparent.`RCNO`
 INNER JOIN `externallendingrelatedsubparent` externallendingrelatedsubparent ON enterprise.`RCNO` = externallendingrelatedsubparent.`RCNO`
 INNER JOIN `debtinstrumentsheldunrelated` debtinstrumentsheldunrelated ON enterprise.`RCNO` = debtinstrumentsheldunrelated.`RCNO`
 INNER JOIN `debtinstrumenturcountry` debtinstrumenturcountry ON enterprise.`RCNO` = debtinstrumenturcountry.`RCNO`
 INNER JOIN `otherexpenditurenr` otherexpenditurenr ON enterprise.`RCNO` = otherexpenditurenr.`RCNO`
 INNER JOIN `depositwithnonresidentbanks` depositwithnonresidentbanks ON enterprise.`RCNO` = depositwithnonresidentbanks.`RCNO`
 INNER JOIN `finderivativecontractwithnr` finderivativecontractwithnr ON enterprise.`RCNO` = finderivativecontractwithnr.`RCNO`
 INNER JOIN `foriegngovtdebtsecuritycountry` foriegngovtdebtsecuritycountry ON enterprise.`RCNO` = foriegngovtdebtsecuritycountry.`RCNO`,
 `dividendearningsprofit` dividendearningsprofit
WHERE
     enterprise.RCNO = 'RC 10000'
share|improve this question

closed as too localized by a_horse_with_no_name, Aaron Bertrand, RolandoMySQLDBA, Paul White, Martin Smith Oct 29 '12 at 21:58

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

    
Wow, Was this query created by a human being? Now, are you sure you have data that satisfies the inner joins? If you are not sure, simplify the query and start with 1 inner join and keep adding them until you get no rows, then you will know which one is causing this (there could be more than 1). –  Emmad Kareem Jul 16 '12 at 9:16
    
Well, that's why it's prone to errors... Have u found it? –  M. Hussaini Jul 16 '12 at 9:18
    
Ok. I'll follow up on that! Does this mean that, if a table does not have data inner joins wont work? –  M. Hussaini Jul 16 '12 at 9:19
5  
You can also change all these INNER joins to LEFT joins. As all tables are on the n side and the enterprise on the 1 side of the 1::n relationship, the Nulls will reveal the truth. –  ypercube Jul 16 '12 at 9:46
1  
"I would like to attach the tables(sql). I can't find a place to upload." You can either use pastebin, or sqlfiddle. SQL Fiddle is probably preferred. –  Derek Downey Jul 17 '12 at 13:18
show 2 more comments

2 Answers 2

When you are building complex queries, you should build them in stages checking the results as you go, not build one whole huge query and then try to figure out what is wrong. Here is what I do.

First I list all the columns I want on a spearate line and comment out all the columns except those in the first table. Then I add the from clause and any where conditions on the first table. I note how many records are returned. Then I add each table one at a time and check the results again. I am especially concerned when the number jumps up really high unexpectedly or goes down unexpectedly.

In the case of the number jumping up high, you may have a one to many relationship that needs further definition. This is especially true if the field or fields you are getting from the table are almost always the same. You may need a derived table or a specific where condition to resolve. You might even want to do some aggregation. Now I'm not saying it always bad if the record counts go up, only if they go up when you didn't expect them to or when the result set looks suspicious.

In the second case, you generally have an inner join where you need a left join. The number of records went down because you did not have a matching record in the joined table. I often check each inner join with a left join to ensure that I return the same number of records. If it does then the inner join is appropriate, if it doesn't then I need to decide if it is filtering records I should be filtering or if I need a left join.

When I am not sure why the record counts are off from what I expect, I use a select * (temporarily) just to see all the data, so I can determine why the data is off. I can almost always tell what the problem is when I see all the data. Do not ever use select * in a multiple join query like this or you will be returning much more data than you need and thus slowing the query as at a minumum the join fields are repeated (plus it is truly unlikely you need every field from 20 joins!).

To find you issue, you are going to have to repeat this process. Start with just the from clause and the where condtions on it and add tables until you find the one (s) which are causing the query to eliminate all records. Don't stop with the first find, it is possible in a long query like this that you have multiple problems.

share|improve this answer
add comment

The reason for this not to work, is that one or more that tables is either empty or have NULLs on the FK column.

Since INNER JOINs require that exists at least one row that conform to the relationship in each table joined, is needed just ONE table that not satisfy this requirement to all statement return 0 rows.

The solution is removing tables from the joins until the culprit shows itself. (Or use LEFT joins)...

share|improve this answer
2  
Beyond empty/NULL, it's possible there's just no relationship in just one of the JOINs –  JNK Jul 27 '12 at 19:07
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.