Hi @patrick, I have been looking at this for most of the day and struggling to get it to work. Here is the MySQL as I would like it to be…
SELECT DISTINCT b.business_id, b.business, b.trading_name, b.notes
FROM tbl_business AS b
WHERE
b.business LIKE :P1 OR
b.trading_name LIKE :P1 OR
b.notes LIKE :P1 OR
EXISTS (
SELECT 1
FROM tbl_people AS p
INNER JOIN link_business_people AS b_p
ON p.people_id = b_p.people_id
AND b_p.business_id = b.business_id <<
WHERE
p.forename LIKE :P1 OR
p.midname LIKE :P1 OR
p.surname LIKE :P1 OR
p.nickname LIKE :P1 OR
p.notes LIKE :P1
) OR
EXISTS (
SELECT 1
FROM tbl_address AS a
INNER JOIN link_business_address AS b_a
ON a.address_id = b_a.address_id
AND b_a.business_id = b.business_id <<
WHERE
a.property LIKE :P1 OR
a.street LIKE :P1 OR
a.locality LIKE :P1 OR
a.post_town LIKE :P1 OR
a.county LIKE :P1 OR
a.postcode LIKE :P1 OR
a.notes LIKE :P1
) OR
EXISTS (
SELECT 1
FROM tbl_comms AS c
INNER JOIN link_business_comms AS b_c
ON c.comms_id = b_c.comms_id
AND b_c.business_id = b.business_id <<
WHERE
c.comms LIKE :P1 OR
c.comms_type LIKE :P1 OR
c.comms_purpose LIKE :P1 OR
c.notes LIKE :P1
)
I have been testing this directly in phpMyAdmin. The error I am getting is this…
#1054 - Unknown column 'b.business_id' in 'on clause'
From what I can tell and from what I have read, MySQL processes the outer expressions (outside of the parenthesis) and passes them inside, which is what we want, EXCEPT where a JOIN is concerned.
So, when the query picks up b.business_id
from the very first line, it cannot pass it to the
AND b_p.business_id = b.business_id
on the 12th, 25th and 40th lines (as marked).
I was convinced this would work. Any other thoughts or suggestions?