I'm writing a search function, which needs to query 4 tables.
I need to run a search through the users table, which queries the types, subtypes and services offered based on the respective id in the users table.
For example:
A row in the users table may be like this:
| id | type | subtype | services_offered | first_name | last_name | contact | company |
| 1 | 1 | 2 | 47 | Gareth | Davies | g@g.com | Gazza |
A row in the types table may be like this:
| id | type |
| 1 | finance |
And so on...
I've got it sort of working, but for some reason it returns about 30 rows for each contact! Here is my SQL.
SELECT
c.type, c.subtype, c.first_name, c.last_name, c.company, c.contact, c.services_provided, c.additional_information, c.date_updated, t.id, t.type, s.id, s.subtype, so.id, so.services_offered
FROM
contacts c, types t, subtypes s, services_offered so
WHERE
((c.type LIKE '%$q%' || c.subtype LIKE '%$q%' || c.first_name LIKE '%$q%' || c.last_name LIKE '%$q%' || c.company LIKE '%$q%' || c.services_provided LIKE '%$q%' || c.contact LIKE '%$q%' || c.additional_information LIKE '%$q%' || t.type LIKE '%$q%' || s.subtype LIKE '%$q%' || so.services_offered LIKE '%$q%')
AND (c.type = t.id || c.subtype = s.id || c.services_provided = so.id))
Ideally it would only return one of each contact!
Any help would be greatly appreciated!
Thanks,
A reasonable guess would be that you need to group the results, so use
GROUP BY c.id
in the end of the query would probably do it