simple search across table(s) in MySQL

[firthusa]

I am trying to do a simple search across my MySQL database of products but I can't seem to get it.

Let say I have two tables: products and brands. The product table has a column called "brand_id" and it correlates to the brand "id" column"

I want to be able to search for products but searching both tables... so if they typed in the name of the product or the brand it would come up.

Usually I would do something like this:

SELECT p.id FROM products AS p, brands AS b WHERE p.brand_id = b.id AND p.title LIKE '%$q%' OR OR b.brand LIKE '%$q%'

But the problem I am running into is that if the search term ($q) is found twice in any of the columns searched it will display the same product twice. I tried adding DISTINCT in front of id but that didnt solve it.

Also... using "WHERE LIKE %%" seems like such a innefficient way of doing this. Is there no way to do a FULL-TEXT search across the two tables? I would love to do a search based on relevance but still keeping brand info in a separate table.

It seems like such a easy and common thing to do but yet I cannot seem to find anyone with a solution.

So... to sum it all up I am looking for: The best way to search across two tables that share a common column id.

[phranque]

welcome to WebmasterWorld [webmasterworld.com], firthusa!

you probably need to add a "LIMIT 1" to your query.

you could also try "EXPLAIN SELECT ..." to see what mysql is doing with your query.

[firthusa]

But adding LIMIT 1 will limit my results to just one result. I want to display all products based on the search but not include any duplicates.

What is the best way to perform a relevant search across two tables that have common primary & secondary keys?