SQL Help

[c_pattle]

I have the following query

 

SELECT product_data.product_id, product_data.product_name, product_pricing.sales_price_inc_vat, products_to_categories.*  FROM product_data, product_pricing, products_to_categories WHERE product_data.product_id = product_pricing.product_id AND product_data.product_id = products_to_categories.product_id LIMIT 10

 

This works fine but the query takes a long time to excecute because it compares the "product_data.product_id" twice to two tables.  Is there a way to do this comparison but to speed up the execution time?

 

Thanks for any help

[awjudd]

Do you have indexes on either of the tables?

 

I would also suggest against using the ANSI JOINs (,) and move to using INNER JOINs because you will not be CROSS JOINing your dataset.

 

SELECT pd.product_id, pd.product_name, pp.sales_price_inc_vat, ptc.* 
FROM product_data pd
JOIN product_pricing pp ON pd.product_id = pp.product_id
JOIN products_to_categories ptc ON pp.product_id = ptc.product_id

 

~awjudd

[kickstart]

Hi

 

Using decent indexes those JOINs should be fairly efficient (although I far prefer using JOINs coded as awjudd has suggested above).

 

All the best

 

Keith