Most efficient way to select from several tables

[BenWeston]

Hi all

 

I have an InnoDB database with three tables – users, treatments and treatments_offered. The users table, predictably, stores user information with an ID for each. The treatments table stores a list of treatments with an ID for each and the treatments_offered table just stores the ID of a user and an ID of a treatment that they offer. Some users may only offer one treatment, some may offer 20+. Foreign keys are in place to make sure all this stays in sync.

 

My question is what is the quickest MySQL query I can run (via PHP but I'm not sure that actually matters), to select and list all the treatments provided by a user of ID, say, 3? Is there one query I can run or do I have to run several separate ones like this:

$treatment_ids = mysql_fetch_array(mysql_query("SELECT treatment_id FROM treatments_offered WHERE user_id = '$user_id';"), MYSQL_ASSOC);

$treatments = array();

foreach ($treatment_ids as $treatment_id) {
$temp = mysql_fetch_array(mysql_query("SELECT treatment FROM treatments WHERE id = '$treatment_id';"), MYSQL_ASSOC);
$treatments[] = $temp['treatment'];
}

That seems VERY inefficient to me – I think I'm missing something!

[mac_gyver]

you would need to use a JOIN'ed query to retrieve related information using one query.

[BenWeston]

Great, thanks! So, perhaps, something like this (if I'm interpreting the JOIN documentation properly)?

SELECT treatment
FROM treatments 
JOIN treatments_offered ON (treatments_offered.treatment_id = treatments.id)
WHERE treatments_offered.user_id = '$user_id';

...as the users table will actually be redundant, seeing as the user's ID has a foreign key to user_id in treatments_offered?

[BenWeston]

For the benefit of anyone else Googling, I can confirm the above SQL query DOES work. Very helpful article on JOINs at the following link: http://www.sitepoint.com/understanding-sql-joins-mysql-database/