I am trying to write a search function for a page and there are many tables related to my franchise table and connected through a franchise table id. So a name of a city can be in the city table but the city is tied to a location table which is tied to the franchise table.
We have an inner join written and it does select the correct rows, but if the term is found in multiple tables or in multiple places in a table, it returns a row for each time that it is found. Is there a way to limit that. I am thinking a group would do it, but not sure where I would say "GROUP BY franchise.franchise_id"
SELECT * FROM franchises
INNER JOIN
locations
ON
locations.franchise_id = franchises.franchise_id
INNER JOIN
operators
ON
operators.operator_id = franchises.operator_id
INNER JOIN
state_owners
ON
state_owners.state_owner_id = franchises.state_owner_id
WHERE
franchises.franchise_name LIKE :term OR
franchises.franchise_status LIKE :term OR
locations.location_name LIKE :term OR
locations.location_state LIKE :term OR
operators.operator_first_name LIKE :term OR
operators.operator_last_name LIKE :term OR
operators.operator_id LIKE :term OR
state_owners.state_owner_first_name LIKE :term OR
state_owners.state_owner_last_name LIKE :term OR
state_owners.state_owner_owned_state LIKE :term
