SQL Query to show domain prices

[smhnaji]

Hello

I've found the SQL query below but it was posted on 2010 and doesn't seem to work now.

SELECT
   tblpricing.currency,
   tblpricing.msetupfee, tblpricing.qsetupfee, tblpricing.ssetupfee, tblpricing.asetupfee, tblpricing.bsetupfee, tblpricing.monthly, tblpricing.quarterly, tblpricing.semiannually, tblpricing.annually, tblpricing.biennially, 
   (SELECT tblcurrencies.prefix FROM tblcurrencies WHERE id = tblpricing.currency) AS curprefix,
   (SELECT tblcurrencies.suffix FROM tblcurrencies WHERE id = tblpricing.currency) AS cursuffix,
   (SELECT tbldomainpricing.extension FROM tbldomainpricing WHERE id = tblpricing.relid) AS extension
FROM 
   tblpricing
WHERE 
   tblpricing.type= 'domainregister'
AND
   !(SELECT tbldomainpricing.extension FROM tbldomainpricing WHERE id = tblpricing.relid)

Our WHMCS is already 7.2

Would you please help with the new query that should show the following result for us?

[Kian]

SELECT
	t1.extension, 
	t1.order, 
	t2.type, 
	t2.msetupfee, 
	t3.prefix, 
	t3.suffix 
FROM 
	tbldomainpricing AS t1 
LEFT JOIN 
	tblpricing AS t2 ON t1.id = t2.relid AND t2.type IN ("domainregister", "domaintransfer", "domainrenew") 
LEFT JOIN 
	tblcurrencies AS t3 ON t2.currency = t3.id 
ORDER BY 
	FIELD (t1.extension, ".com", ".net") DESC, 
	`order` DESC, 
	t1.extension 

Tips:

• It supports multiple currencies therefore you'll get all the available prices
• Records respect the order you have in Domain Pricing but you can override the default ordering like I did for .com and .net that will always appear on top