SELECT company.id, company.name as name, company.city as city, company.user_id, company.site as site, company.address as address,
  IFNULL(GROUP_CONCAT(distinct(activity.name) SEPARATOR ', '),'не указано') as activity,
  IFNULL(GROUP_CONCAT(DISTINCT(mark.name) SEPARATOR ', '),'не указано') as marks,
  IFNULL(GROUP_CONCAT(DISTINCT(production.name) SEPARATOR ', '),'не указано') as production,
  IFNULL(GROUP_CONCAT(distinct(country.name) SEPARATOR ', '),'не указано') as country, result.name as result, result.id as resultId, company.comment,
  IF(CHAR_LENGTH(TRIM(IFNULL(company.stock, "")))>0,TRIM(IFNULL(company.stock, "Не указано")),"Не указано") as stock, company.interest, company.email,
  company.dispatch
FROM company
  LEFT JOIN activity_link as al on company.id = al.company_id AND al.deleted = 0
  LEFT JOIN activity on activity.id = al.activity_id
  LEFT JOIN production_link as pl on company.id = pl.company_id AND pl.deleted = 0
  LEFT JOIN production on production.id = pl.production_id
  LEFT JOIN country_link as cl on company.id = cl.company_id AND cl.deleted = 0
  LEFT JOIN country on country.id = cl.country_id
  LEFT JOIN result on company.result_id = result.id
  LEFT JOIN mark_link as ml on company.id = ml.company_id AND ml.deleted = 0
  LEFT JOIN mark on mark.id = ml.mark_id
WHERE company.deleted = 0
GROUP BY company.id
ORDER BY company.id DESC LIMIT 0, 30

JOIN 
(
SELECT c1.id, COALESCE(GROUP_CONCAT(DISTINCT activity.name SEPARATOR ', '),'не указано') as activity,
FROM company c1
LEFT JOIN activity_link as al on c1.id = al.company_id AND al.deleted = 0
LEFT JOIN activity on activity.id = al.activity_id
WHERE c1.deleted = 0
GROUP BY c1.id
LIMIT 0, 30
)

JOIN 
(
SELECT c1.id, COALESCE(GROUP_CONCAT(DISTINCT activity.name SEPARATOR ', '),'не указано') as activity,
FROM company c1
LEFT JOIN activity_link as al on c1.id = al.company_id AND al.deleted = 0
LEFT JOIN activity on activity.id = al.activity_id
WHERE c1.deleted = 0
GROUP BY c1.id
ORDER BY c1.id DESC
LIMIT 0, 30
)

SELECT 
-- поля из основной таблицы
com0.id, 
com0.name, 
com0.user_id, 
com0.site, 
com0.address,
com0.comment,
IF(CHAR_LENGTH(TRIM(IFNULL(com0.stock, "")))>0,TRIM(IFNULL(com0.stock, "Не указано")),"Не указано") as stock,
com0.interest,
com0.email,
com0.dispatch,
-- расчётное поле из первого подзапроса
activity.activity,
mark.marks,
production.production,
country.country,
result.name as result, result.id as resultId
-- поля из других подзапросов
-- 
FROM company com0
-- первый подзапрос
JOIN 
(
SELECT c1.id, COALESCE(GROUP_CONCAT(DISTINCT activity.name SEPARATOR ', '),'не указано') as activity
FROM company c1
LEFT JOIN activity_link as al on c1.id = al.company_id AND al.deleted = 0
LEFT JOIN activity on activity.id = al.activity_id
WHERE c1.deleted = 0
GROUP BY c1.id
ORDER BY c1.id DESC
LIMIT 0, 30
) activity ON activity.id = com0.id
JOIN 
(
SELECT c2.id, COALESCE(GROUP_CONCAT(DISTINCT mark.name SEPARATOR ', '),'не указано') as marks
FROM company c2
LEFT JOIN mark_link as ml on c2.id = ml.company_id AND ml.deleted = 0
LEFT JOIN mark on mark.id = ml.mark_id
WHERE c2.deleted = 0
GROUP BY c2.id
ORDER BY c2.id DESC
LIMIT 0, 30
) mark ON mark.id = com0.id
JOIN 
(
SELECT c3.id, COALESCE(GROUP_CONCAT(DISTINCT production.name SEPARATOR ', '),'не указано') as production
FROM company c3
LEFT JOIN production_link as pl on c3.id = pl.company_id AND pl.deleted = 0
LEFT JOIN production on production.id = pl.production_id
WHERE c3.deleted = 0
GROUP BY c3.id
ORDER BY c3.id DESC
LIMIT 0, 30
) production ON production.id = com0.id
JOIN 
(
SELECT c4.id, COALESCE(GROUP_CONCAT(DISTINCT country.name SEPARATOR ', '),'не указано') as country
FROM company c4
LEFT JOIN country_link as cl on c4.id = cl.company_id AND cl.deleted = 0
LEFT JOIN country on country.id = cl.country_id
WHERE c4.deleted = 0
GROUP BY c4.id
ORDER BY c4.id DESC
LIMIT 0, 30
) country ON country.id = com0.id
JOIN result on com0.result_id = result.id
LIMIT 0, 30