How to get all active product/services with client name and paid amout, tax details.

[bagfuloz]

How to get all active product/services with client name and paid amout, tax details.

I am trying by this, but its not working. 

SELECT
 CONCAT(firstname,' ',lastname) AS Name,
 tblclients.companyname,
 tblproducts.name AS Product,
 tblhosting.domain,
 tblhosting.paymentmethod,
 tblhosting.billingcycle,
 tblhosting.amount,
 tblorders.status
FROM tblhosting
  INNER JOIN tblorders
  ON tblhosting.packageid = tblorders.id
 INNER JOIN tblproducts
   ON tblhosting.packageid = tblproducts.id
 INNER JOIN tblclients
   ON tblhosting.userid = tblclients.id
WHERE tblorders.status = 'Active'
ORDER BY Name

I am trying to getting product data by tblorders. but no luck.

Please anyone tell me, How to get this done.

 

Thanks

Madan

[brian!]

Hi Madan,

On 12/21/2017 at 12:12, bagfuloz said:

How to get all active product/services with client name and paid amount, tax details.

in this instance, I would avoid using tblorders - there are scenarios where a hosting product wouldn't contain a valid order ID (e.g if it's been imported from WHM), so try using the domainstatus value in tblhosting instead.

SELECT
 CONCAT(firstname,' ',lastname) AS Name,
 tblclients.companyname,
 tblproducts.name AS Product,
 tblhosting.domain,
 tblhosting.paymentmethod,
 tblhosting.billingcycle,
 tblhosting.amount,
 tblhosting.domainstatus
FROM tblhosting
 INNER JOIN tblproducts
   ON tblhosting.packageid = tblproducts.id
 INNER JOIN tblclients
   ON tblhosting.userid = tblclients.id
WHERE tblhosting.domainstatus = 'Active'
ORDER BY Name