MySQL Searches on multiple tables

Searching combining data from multiple tables

[TheCatReturns]

Okay, I'm trying to figure out the best way to accomplish what seems like it should be a common problem.

Essentially, I have 2 main tables: a table of Products (roughly 30,000), and a table of Properties (roughly 400).

These are related in a many-to-many fashion: that is, a given Product can have multiple Properties, and a given Property can be shared by multiple Products.

I want to know the quickest way, for example, to "select * from products order by adddate, id" where the product has a particular property. Likewise I need to get the full Property rows for every property of a given product.

I have a feeling the solution involves creating a third table to represent the relationships between products. E.g.:

ProdID ¦ PropID 
---------------- 
 1 ¦ 1 
 1 ¦ 5 
 1 ¦ 7 
 2 ¦ 1 
 2 ¦ 4 
 3 ¦ 4 

Would mean product 1 has properties 1,5,7, product 2 has properties 1 and 4, and product 3 has property 4.

I'm just not sure how to use this info to do the selects in a single step. Of course, I could do one select on this relations table to get all the products with property 4, and then for each result, do a select in the product table to get the full info. I have to believe there's an easier and quicker way to accomplish this.

[FalseDawn]

You are correct, a many-many join is most easily represented in a physical database schema with a "middle man" table, having the keys of the 2 tables partaking in the many-many relationship as foreign keys.

I have to believe there's an easier and quicker way to accomplish this

There is - simply join the products table to the linking table (which I'll call ProdProps here)

SELECT P.prodname,P.adddate,P.id FROM products P INNER JOIN ProdProps PP
ON P.id=PP.ProdID
WHERE PP.PropID=*yourpropertyID*
ORDER BY P.adddate, P.id

[TheCatReturns]

Thank you, that was just what I was looking for :)