Hello. There are 3 tables in the database.

MODELS `MOD_ID` int(11) NOT NULL,`MOD_MFA_ID` smallint(6) ,`MOD_CDS_ID` int(11) DESIG `CDS_ID` int(11) NOT NULL, `CDS_TEX_ID` int(11) NOT NULL TEXT `TEX_ID` int(11) NOT NULL,`TEX_TEXT` varchar(1200)

How can I get the TEX_TEXT parameter TEX_TEXT from the third table (TEXTS), if the relationship is: MODELS parameter MOD_CDS_ID => parameter from the DESIG table CDS_ID And the parameter from the DESIG table CDS_TEX_ID = parameter from the third TEXTS table TEX_ID

That is, you need to get the following data: From the MODELS => MOD_CDS_ID
From DESIG table => CDS_ID = MOD_CDS_ID , CDS_TEX_ID
From table TEXTS => TEX_ID = CDS_TEX_ID , TEX_TEXT

Trying to do this:

 select * from TOF_MODELS JOIN TOF_COUNTRY_DESIGNATIONS ON TOF_MODELS.MOD_CDS_ID = TOF_COUNTRY_DESIGNATIONS.CDS_ID JOIN TOF_DES_TEXTS ON TOF_COUNTRY_DESIGNATIONS.CDS_TEX_ID = TOF_DES_TEXTS.TEX_ID WHERE TOF_MODELS.MOD_MFA_ID = 647

Displays 0 lines

  • both join'a on left join change, it seems in the database "broken" links. - Yura Ivanov
  • Yes, it seems to be output correctly. How to make such a request a work - he hangs completely base)) SELECT TOF_MODELS.MOD_ID, TOF_MODELS.MOD_MFA_ID, TOF_MODELS.MOD_CDS_ID , TOF_COUNTRY_DESIGNATIONS.CDS_ID, TOF_COUNTRY_DESIGNATIONS.CDS_TEX_ID, TOF_DES_TEXTS.TEX_ID, TOF_DES_TEXTS.TEX_TEXT FROM TOF_MODELS INNER JOIN TOF_COUNTRY_DESIGNATIONS ON TOF_COUNTRY_DESIGNATIONS.CDS_ID = TOF_MODELS.MOD_CDS_ID INNER JOIN TOF_DES_TEXTS ON TOF_COUNTRY_DESIGNATIONS.CDS_TEX_ID = TOF_DES_TEXTS.TEX_ID WHERE TOF_MODELS.MOD_MFA_ID = '. $ Id.' ORDER BY TOF_DES_TEXTS.TEX_TEXT - Batyabest
  • Indices do you have any thread? - Yura Ivanov
  • Yes, I indexed some fields and everything began to work. Thank. Change comments in response. - Batyabest

1 answer 1

The real question here is not “how to get the associated data,” because the answer to this question is trivial — of course, the join. And the author himself has already answered this question.

And the question is, in fact, “why this question returns zero lines to me”. And the answer to this question is also obvious - because the database has no corresponding records.

You can check yourself with your hands. Choose

 select * from TOF_MODELS WHERE TOF_MODELS.MOD_MFA_ID = 647

and get (or not get) a list of IDs. further substitute this list in the following table. And so on. The algorithm is simple as 2 kopecks, no stackerflow is needed for it. You just need to believe your eyes and act according to common sense.

  • select * from TOF_MODELS WHERE TOF_MODELS.MOD_MFA_ID = 647 prints 106 lines - Batyabest
  • And can all stupid because of the huge number of records? DESIG table has about 6 million rows. - Batyabest