I have a query which gets the information that I want using SUBSTRING_INDEX
in MySQL. I am trying to convert this to use the Joomla query building syntax, but I am not sure how it should be formatted?
Here is my query:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`file_url`, '/', -1), ".", 1),"-",1) AS product_sku, `virtuemart_media_id`, `virtuemart_product_id` FROM `jos_virtuemart_medias` INNER JOIN `jos_virtuemart_products` ON SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(`jos_virtuemart_medias`.`file_url`, '/', -1), ".", 1),"-",1) = `jos_virtuemart_products`.`product_sku` WHERE 1
I'm getting stuck at the first hurdle as I really can't figure out how to implement the SELECT statement?
Any advise would be most appreciated? I have had a look at the Joomla docs, but it doesn't cover the inclusion of MySQL functions.