Searching multiple tables with Subquery

Question

Hello,

What I need to do is searching from companies and tags table and listing companies.

My table structure is as follows;

tags (tag_ID, tag) tag_relation (tag_ID, company_ID) companies (company_ID, company_name, company_description)

The query should be able to search both company info (name, description) and tags. If tag searched, related companies should return.

Answer 1

Assuming that you want to run "like" searches you could use the code below. If you have very large tables the performance may be pretty bad because the wild cards will prevent the use of indexes.

select     companies.company_id, companies.company_name, companies.company_description
from       companies
inner join tag_relation
on         companies.company_id = tag_relation.company_id
inner join tags
on         tags.tag_id = tag_relation.tag_id
where      companies.company_name like '%something%'
or         companies.company_description like '%something%'
or         tag.tag like '%something%'
group by   companies.company_id, companies.company_name, companies.company_description
order by   companies.company_name