MySQL Joins

[rhowes]

Hi

Can you have multiple (nested?) joins in MySQL? (i.e. accross three tables for example).

Thanks,
Cheers
Richard

 

[Copps]

You can have multiple joins. The following code works.

SELECT rf_currency.rf_currency_name
FROM ((rf_currency INNER JOIN ac_accomm_rate ON rf_currency.rf_currency_id = ac_accomm_rate.rf_currency_id)
INNER JOIN rf_tax_rate ON rf_tax_rate.rf_tax_rate_id = ac_accomm_rate.rf_tax_rate_id)
WHERE ac_accomm_rate.ac_accomm_rate_id="1";

This code was changed from the following code generated by MS-Access which did not work.

SELECT rf_currency.rf_currency_name
FROM rf_tax_rate INNER JOIN (rf_currency INNER JOIN ac_accomm_rate ON rf_currency.rf_currency_id = ac_accomm_rate.rf_currency_id) ON rf_tax_rate.rf_tax_rate_id = ac_accomm_rate.rf_tax_rate_id
WHERE (((ac_accomm_rate.ac_accomm_rate_id)=1));

 

[doddo]

Hi, I read this with interest. Has anybody noticed really bad performance when trying to do such joins with Mysql?
A simple example of a join below

SELECT Raw.Category, Raw.Filename
FROM Raw LEFT JOIN Marker ON Raw.Category = Marker.MarkerName
GROUP BY Raw.Category, Raw.Filename, Marker.MarkerName
HAVING (((Marker.MarkerName) Is Null))
ORDER BY Raw.Filename;

where Raw contains about 1 million small records and Marker about 400.

This takes over ten minutes!

Regards
Richard