MYSQL statement, getting specific data from 2 tables

[sc]

Hi all.

I Have a list of directory companies within a database, along with a directory category table, and a category/company link table(as companies may be subscribed to more than one 'directory category').

i would like my users to be able to search for companies in my table using keyword,company name and/or location, used against columns in the 'companies' table. (this is easy, working fine)

i also want to be able to search by category as well as by any company keywords, which means detecting if a certain company_id is linked to a certain category_id in the cat_link table. How may i accomplish this? i understand using multiple table searching to some extent, but cannot work out the statement i need. Please help.

hope ive made myself clear, if not please ask me to clarify.

MY BASIC TABLE SETUP---------------

COMPANIES                    CAT_LINK                    CATEGORY
---------------------------------------------------------------
company_id                    company_id                 category_id
name(fulltext)                 category_id                  category name
address
description(fulltext)
etc


----------------------------------------

anther explanation...

a user enters a search keyword, which is used against companies.name/companies.description. User also specifies a category, so ......  results compiled for the keyword search, then THOSE results searched for companies matching the specified category (which entails looking at category_link table)

[artacus]

[code]
SELECT co.*
FROM CATEGORY AS cat
JOIN CAT_LINK AS cl ON cat.category_id = cl.category_id
JOIN COMPANIES AS co ON cl.company_id = co.company_id
WHERE cat.category_name LIKE '%$search%'
ORDER BY co.name
[/code]

[sc]

thanks.

could i ask for an explanation please?


SC ???

[fenway]

The join conditions simply link the related tables based on the shared field... and the remainder of the where clause is just the search itself.