SELECT DISTINCT subjekty.id, name_subjekt,name_person, surname_person, title_before, perex, title_behind, DATE_FORMAT(zobrazit_od,'%d.%m.%Y') AS zobrazit_od, (SELECT odkaz FROM kategorie WHERE kategorie.id=(SELECT id_kategorie FROM subjekty_kategorie WHERE id_subjekt=subjekty.id ORDER BY id ASC LIMIT 0,1)) AS odkaz_kategorie, (SELECT count(*) FROM doporuceni WHERE id_doporuceny=subjekty.id AND stav='p') AS pocet_star
FROM subjekty
LEFT JOIN subjekty_kraje ON subjekty_kraje.id_subjekt=subjekty.id
WHERE ('2025-04-24' between zobrazit_od AND zobrazit_do)
AND published='y' AND subjekty_kraje.value=1 AND perex<>''
AND subjekty_kraje.id_kraj=12
AND subjekty.id IN (SELECT id_subjekt FROM subjekty_kategorie WHERE id_kategorie=(SELECT id FROM kategorie WHERE odkaz='remesla-a-odborne-firmy'))
AND subjekty.id IN (SELECT id_subjekt FROM subjekty_parametry WHERE (id_hodnota IN (SELECT id FROM cis_parametry_hodnoty WHERE parametr_id IN (SELECT id FROM cis_parametry WHERE name LIKE 'odborne firmy' COLLATE utf8_general_ci)) OR value IN (SELECT id FROM cis_parametry_hodnoty WHERE parametr_id IN (SELECT id FROM cis_parametry WHERE name LIKE 'odborne firmy' COLLATE utf8_general_ci))))
AND subjekty.id IN (SELECT id_subjekt FROM subjekty_parametry WHERE value IN (SELECT id FROM cis_parametry_hodnoty WHERE jmeno LIKE 'financni a dotacni poradenstvi' COLLATE utf8_general_ci AND parametr_id=(SELECT id FROM cis_parametry WHERE name LIKE 'odborne firmy' COLLATE utf8_general_ci)) OR id_hodnota IN (SELECT id FROM cis_parametry_hodnoty WHERE jmeno LIKE 'financni a dotacni poradenstvi' COLLATE utf8_general_ci AND parametr_id=(SELECT id FROM cis_parametry WHERE name LIKE 'odborne firmy' COLLATE utf8_general_ci)))
ORDER BY datum_upravy DESC
limit 0, 15
Ve Vámi zvoleném regionu se v této kategorii zatím žádný subjekt neregistroval.
Buďte první a registrujte se nebo nám doporučte zkušenou osobu, firmu či organizaci.