SELECT
res.bedr_id,
SUM(res.amount) as score,
bedrijf.*
FROM (SELECT bedr_id, MATCH (bedr_naam, bedr_titel, bedr_beschrijving) AGAINST ('delfstoffenwinning') as amount FROM bedrijf WHERE MATCH (bedr_naam, bedr_titel, bedr_beschrijving) AGAINST ('delfstoffenwinning')
UNION ALL
SELECT nws_bedr_id as bedr_id, MATCH (nws_titel, nws_subtitel, nws_tekst) AGAINST ('delfstoffenwinning') as amount FROM nieuws WHERE MATCH (nws_titel, nws_subtitel, nws_tekst) AGAINST ('delfstoffenwinning')
UNION ALL
SELECT key_bedr_id as bedr_id, MATCH (key_woord) AGAINST ('delfstoffenwinning') as amount FROM keywords WHERE MATCH (key_woord) AGAINST ('delfstoffenwinning')
UNION ALL
SELECT adv_bedr_id as bedr_id, MATCH (adv_titel, adv_subtitel, adv_tekst) AGAINST ('delfstoffenwinning') as amount FROM advertenties WHERE MATCH (adv_titel, adv_subtitel, adv_tekst) AGAINST ('delfstoffenwinning')
UNION ALL
SELECT bs_bedr_id AS bedr_id, 1 AS amount FROM subrubrieken JOIN bedrijf_subrubriek ON subr_id = bs_subr_id WHERE MATCH (subr_naam)AGAINST ('delfstoffenwinning')
UNION ALL
SELECT bedr_id, 1 as amount FROM bedrijf WHERE bedr_rubr_id IN(SELECT rubr_id FROM rubrieken WHERE MATCH (rubr_naam) AGAINST ('delfstoffenwinning')) UNION ALL SELECT bedr_id, MATCH (bedr_naam, bedr_titel, bedr_beschrijving) AGAINST ('en') as amount FROM bedrijf WHERE MATCH (bedr_naam, bedr_titel, bedr_beschrijving) AGAINST ('en')
UNION ALL
SELECT nws_bedr_id as bedr_id, MATCH (nws_titel, nws_subtitel, nws_tekst) AGAINST ('en') as amount FROM nieuws WHERE MATCH (nws_titel, nws_subtitel, nws_tekst) AGAINST ('en')
UNION ALL
SELECT key_bedr_id as bedr_id, MATCH (key_woord) AGAINST ('en') as amount FROM keywords WHERE MATCH (key_woord) AGAINST ('en')
UNION ALL
SELECT adv_bedr_id as bedr_id, MATCH (adv_titel, adv_subtitel, adv_tekst) AGAINST ('en') as amount FROM advertenties WHERE MATCH (adv_titel, adv_subtitel, adv_tekst) AGAINST ('en')
UNION ALL
SELECT bs_bedr_id AS bedr_id, 1 AS amount FROM subrubrieken JOIN bedrijf_subrubriek ON subr_id = bs_subr_id WHERE MATCH (subr_naam)AGAINST ('en')
UNION ALL
SELECT bedr_id, 1 as amount FROM bedrijf WHERE bedr_rubr_id IN(SELECT rubr_id FROM rubrieken WHERE MATCH (rubr_naam) AGAINST ('en')) UNION ALL SELECT bedr_id, MATCH (bedr_naam, bedr_titel, bedr_beschrijving) AGAINST ('delfstoffen') as amount FROM bedrijf WHERE MATCH (bedr_naam, bedr_titel, bedr_beschrijving) AGAINST ('delfstoffen')
UNION ALL
SELECT nws_bedr_id as bedr_id, MATCH (nws_titel, nws_subtitel, nws_tekst) AGAINST ('delfstoffen') as amount FROM nieuws WHERE MATCH (nws_titel, nws_subtitel, nws_tekst) AGAINST ('delfstoffen')
UNION ALL
SELECT key_bedr_id as bedr_id, MATCH (key_woord) AGAINST ('delfstoffen') as amount FROM keywords WHERE MATCH (key_woord) AGAINST ('delfstoffen')
UNION ALL
SELECT adv_bedr_id as bedr_id, MATCH (adv_titel, adv_subtitel, adv_tekst) AGAINST ('delfstoffen') as amount FROM advertenties WHERE MATCH (adv_titel, adv_subtitel, adv_tekst) AGAINST ('delfstoffen')
UNION ALL
SELECT bs_bedr_id AS bedr_id, 1 AS amount FROM subrubrieken JOIN bedrijf_subrubriek ON subr_id = bs_subr_id WHERE MATCH (subr_naam)AGAINST ('delfstoffen')
UNION ALL
SELECT bedr_id, 1 as amount FROM bedrijf WHERE bedr_rubr_id IN(SELECT rubr_id FROM rubrieken WHERE MATCH (rubr_naam) AGAINST ('delfstoffen'))) as res
JOIN
bedrijf ON res.bedr_id = bedrijf.bedr_id JOIN gebruikers ON bedrijf.bedr_id = geb_bedr_id WHERE bedr_active = 1 AND bedr_rubr_id = '272'
GROUP BY
res.bedr_id
ORDER BY geb_type DESC, score DESC;