Long SQL I want to understand and split

[JAScarb]

Hi,

This SQL (from OS Commerce) is resulting in zero matches, while another with a different p2c.categories_id results in matches and I don't know why.

I was never good with left-joins, so I wonder if someone could help me split this up into chunks I can run to help me understand it.

Here's the SQL:
select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from ((products_description pd, products p) left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c) left join specials s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '3' order by pd.products_name

I guess it creates some tables on the fly and then compares those, I just don't know how to split this up so I can run it and see those intermediate results tables to see where the problem lies.

If anyone wants to write what it's doing in English, that would be super smashing lovely too.

Cheers
J

 

[r937]

the first stage in deconstructing a query is to understand the joins in the FROM clause, with "look ahead" to the conditions in the WHERE clause that might affect row retrieval during the joins

this task is aided immensely by judicious use of indentation and line breaks

select p.products_image
     , pd.products_name
     , p.products_id
     , p.manufacturers_id
     , p.products_price
     , p.products_tax_class_id
     , IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price
     , IF(s.status, s.specials_new_products_price, p.products_price) as final_price 
  from (
       (
       products_description pd
     , products p
       ) 
left 
  join manufacturers m 
    on p.manufacturers_id = m.manufacturers_id
     , products_to_categories p2c
       ) 
left 
  join specials s 
    on p.products_id = s.products_id 
 where p.products_status = '1' 
   and p.products_id = p2c.products_id 
   and pd.products_id = p2c.products_id 
   and pd.language_id = '1' 
   and p2c.categories_id = '3' 
order 
    by pd.products_name

this query pulls all products in category 3 that have language 1 and status 1

if no products are being returned, then there aren't any products in that category that meet those criteria

the IFs in the SELECT clause determine whether special pricing applies

as to efficacy, that FROM clause is a dog's breakfast, one of the worst i've seen, and i'm surprised it works

also, the manufacturers table appears not to be needed at all

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[JAScarb]

Well, perhaps it doesn't work, that's the point. Thanks enormously, I'll grab some concentration time later and work through what you've said. Much appreciated