select
p.products_image, p.products_image2, pd.products_name, p.products_price, p.products_id, pd.products_long_name, SUBSTRING_INDEX(pd.products_description, '.' , 1) as products_description,
pd.products_href, 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 p,
products_description pd
left join specials s
on pd.products_id = s.products_id
join products_to_products_tags p2pt
on pd.products_id = p2pt.products_id
join products_tags pt
on p2pt.products_tags_id = pt.products_tags_id
where p.products_status = '1'
and p2pt.products_to_products_tags_status = '1'
and pt.products_tags_status = '1'
and p.products_id = pd.products_id
and pt.products_tags_name = 'skinhead'
and pd.language_id = '1'
order by p.products_ordered DESC, pd.products_name
limit 0, 72