SELECT
SEARCH_TREE.STR_LEVEL,
ELT(SEARCH_TREE.STR_LEVEL, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT,
ELT(SEARCH_TREE.STR_LEVEL, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID,
SEARCH_TREE.STR_ID_PARENT AS STR_PARENT,
ELT(SEARCH_TREE.STR_LEVEL-1, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT2,
ELT(SEARCH_TREE.STR_LEVEL-1, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID2,
ELT(SEARCH_TREE.STR_LEVEL-2, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT3,
ELT(SEARCH_TREE.STR_LEVEL-2, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID3,
ELT(SEARCH_TREE.STR_LEVEL-3, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT4,
ELT(SEARCH_TREE.STR_LEVEL-3, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID4,
ELT(SEARCH_TREE.STR_LEVEL-4, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT5,
ELT(SEARCH_TREE.STR_LEVEL-4, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID5
FROM
SEARCH_TREE
LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = SEARCH_TREE.STR_DES_ID AND DESIGNATIONS.DES_LNG_ID = '.$LNG_ID.'
LEFT JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
LEFT JOIN SEARCH_TREE AS SEARCH_TREE2 ON SEARCH_TREE2.STR_ID = SEARCH_TREE.STR_ID_PARENT
LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = SEARCH_TREE2.STR_DES_ID AND DESIGNATIONS2.DES_LNG_ID = '.$LNG_ID.'
LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID
LEFT JOIN SEARCH_TREE AS SEARCH_TREE3 ON SEARCH_TREE3.STR_ID = SEARCH_TREE2.STR_ID_PARENT
LEFT JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = SEARCH_TREE3.STR_DES_ID AND DESIGNATIONS3.DES_LNG_ID = '.$LNG_ID.'
LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS3.DES_TEX_ID
LEFT JOIN SEARCH_TREE AS SEARCH_TREE4 ON SEARCH_TREE4.STR_ID = SEARCH_TREE3.STR_ID_PARENT
LEFT JOIN DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID = SEARCH_TREE4.STR_DES_ID AND DESIGNATIONS4.DES_LNG_ID = '.$LNG_ID.'
LEFT JOIN DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS4.DES_TEX_ID
LEFT JOIN SEARCH_TREE AS SEARCH_TREE5 ON SEARCH_TREE5.STR_ID = SEARCH_TREE4.STR_ID_PARENT
LEFT JOIN DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID = SEARCH_TREE5.STR_DES_ID AND DESIGNATIONS5.DES_LNG_ID = '.$LNG_ID.'
LEFT JOIN DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS5.DES_TEX_ID
Вот запрос для вывода полного списка категорий в базе, но как их связать с нужным мне $TYP_ID?
Пробовал запрос:
SELECT
SEARCH_TREE.STR_LEVEL,
ELT(SEARCH_TREE.STR_LEVEL, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT,
ELT(SEARCH_TREE.STR_LEVEL, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID,
SEARCH_TREE.STR_ID_PARENT AS STR_PARENT,
ELT(SEARCH_TREE.STR_LEVEL-1, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT2,
ELT(SEARCH_TREE.STR_LEVEL-1, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID2,
ELT(SEARCH_TREE.STR_LEVEL-2, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT3,
ELT(SEARCH_TREE.STR_LEVEL-2, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID3,
ELT(SEARCH_TREE.STR_LEVEL-3, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT4,
ELT(SEARCH_TREE.STR_LEVEL-3, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID4,
ELT(SEARCH_TREE.STR_LEVEL-4, DES_TEXTS.TEX_TEXT, DES_TEXTS2.TEX_TEXT, DES_TEXTS3.TEX_TEXT, DES_TEXTS4.TEX_TEXT, DES_TEXTS5.TEX_TEXT) AS STR_TEXT5,
ELT(SEARCH_TREE.STR_LEVEL-4, SEARCH_TREE.STR_ID, SEARCH_TREE2.STR_ID, SEARCH_TREE3.STR_ID, SEARCH_TREE4.STR_ID, SEARCH_TREE5.STR_ID) AS STR_ID5
FROM
SEARCH_TREE
LEFT JOIN DESIGNATIONS ON DESIGNATIONS.DES_ID = SEARCH_TREE.STR_DES_ID AND DESIGNATIONS.DES_LNG_ID = '.$LNG_ID.'
LEFT JOIN DES_TEXTS ON DES_TEXTS.TEX_ID = DESIGNATIONS.DES_TEX_ID
LEFT JOIN SEARCH_TREE AS SEARCH_TREE2 ON SEARCH_TREE2.STR_ID = SEARCH_TREE.STR_ID_PARENT
LEFT JOIN DESIGNATIONS AS DESIGNATIONS2 ON DESIGNATIONS2.DES_ID = SEARCH_TREE2.STR_DES_ID AND DESIGNATIONS2.DES_LNG_ID = '.$LNG_ID.'
LEFT JOIN DES_TEXTS AS DES_TEXTS2 ON DES_TEXTS2.TEX_ID = DESIGNATIONS2.DES_TEX_ID
LEFT JOIN SEARCH_TREE AS SEARCH_TREE3 ON SEARCH_TREE3.STR_ID = SEARCH_TREE2.STR_ID_PARENT
LEFT JOIN DESIGNATIONS AS DESIGNATIONS3 ON DESIGNATIONS3.DES_ID = SEARCH_TREE3.STR_DES_ID AND DESIGNATIONS3.DES_LNG_ID = '.$LNG_ID.'
LEFT JOIN DES_TEXTS AS DES_TEXTS3 ON DES_TEXTS3.TEX_ID = DESIGNATIONS3.DES_TEX_ID
LEFT JOIN SEARCH_TREE AS SEARCH_TREE4 ON SEARCH_TREE4.STR_ID = SEARCH_TREE3.STR_ID_PARENT
LEFT JOIN DESIGNATIONS AS DESIGNATIONS4 ON DESIGNATIONS4.DES_ID = SEARCH_TREE4.STR_DES_ID AND DESIGNATIONS4.DES_LNG_ID = '.$LNG_ID.'
LEFT JOIN DES_TEXTS AS DES_TEXTS4 ON DES_TEXTS4.TEX_ID = DESIGNATIONS4.DES_TEX_ID
LEFT JOIN SEARCH_TREE AS SEARCH_TREE5 ON SEARCH_TREE5.STR_ID = SEARCH_TREE4.STR_ID_PARENT
LEFT JOIN DESIGNATIONS AS DESIGNATIONS5 ON DESIGNATIONS5.DES_ID = SEARCH_TREE5.STR_DES_ID AND DESIGNATIONS5.DES_LNG_ID = '.$LNG_ID.'
LEFT JOIN DES_TEXTS AS DES_TEXTS5 ON DES_TEXTS5.TEX_ID = DESIGNATIONS5.DES_TEX_ID
WHERE
EXISTS (
SELECT * FROM LINK_GA_STR
INNER JOIN LINK_LA_TYP ON LAT_TYP_ID ='.$TYP_ID.' AND LAT_GA_ID = LGS_GA_ID
INNER JOIN LINK_ART ON LA_ID = LAT_LA_ID
WHERE
LGS_STR_ID = STR_ID
LIMIT 1)
Вроде отсортировала, но все равно были категории которых быть не должно в сравнении с запросом из первого поста