Query with a query or a sub query of some sort

Hi all,

I’m working on an addon for a CRM that will pull related tax charge information into a sales report but the related information table may have several entries (multiple taxes) for the same invoice number and may need to be hit several times to give all of the info. Furthermore I’m making this as dynamic as possible so you may set up just one tax ever or you may have several so the sub-query (if that’s the right term) will need to be dynamic as well. Here’s a simplified example of what I want to do:


SELECT invoice_number, transaction_type, amount FROM tbl_sales; 

This returns


-------------------------------------------------
| invoice_number      | transaction_type  | amount|
-------------------------------------------------
|000700234            | Donation          |    10 |
|000700265            | Membership        |    50 |
|000700253            | Pledge            |    25 |
|000700275            | Event Registration|    15 |
|000700226            | Donation          |    20 |
-------------------------------------------------

The above query just returned the total results. In order to find out how much tax we charged we need to LEFT JOIN on the tbl_tax_invoicing table and pull out the pre_tax value as well as the total_tax charged. I’ll change the query so it looks like the following. Note that because there may be several taxes applicable for a particular transaction_type, I am using SUM(tbl_tax_invoicing.tax_charged) in my query.


SELECT 
    tbl_sales.invoice_number AS invoice_number, 
    tbl_sales.transaction_type AS transaction_type, 
    tbl_sales.amount AS amount, 
    tbl_tax_invoicing.pre_tax AS pre_tax, 
    SUM(tbl_tax_invoicing.tax_charged) AS tax_charged 
FROM tbl_sales 
LEFT JOIN tbl_tax_invoicing
  ON tbl_sales.invoice_number = tbl_tax_invoicing.invoice_number;

The Results are:


------------------------------------------------------------------------
| invoice_number      | transaction_type  | amount|  pre_tax|  total_tax|
------------------------------------------------------------------------
|000700234            | Donation          |    10 |      09 |        01 |
|000700265            | Membership        |    50 |      45 |        05 |
|000700253            | Pledge            |    25 |      23 |        02 |
|000700275            | Event Registration|    15 |      14 |        01 |
|000700226            | Donation          |    20 |      19 |        01 |
------------------------------------------------------------------------

SO that’s all fine and well but now I want to add a couple of dynamic columns to the results because (hypothetically) there are two tax types that apply to Memberships and Event Signups and a single tax for Donations. I can get the tax types out of my tbl_tax_type table and the tax_type_id (tax_type.id) is logged in my tbl_tax_invoicing table so it seems to me that inside my query, I need a sub query for each iteration of the main query (based on invoice number) that selects the results for tax_1 and tax_2 (which are dynamic and could be one or many taxes in tbl_tax_type) and if there are results they need to be inserted into the main query so that the results are:


----------------------------------------------------------------------------------------
| invoice_number      | transaction_type  | amount|  pre_tax|  total_tax|  tax_1|  tax_2|
----------------------------------------------------------------------------------------
|000700234            | Donation          |    10 |      09 |        01 |    01 |  NULL |
|000700265            | Membership        |    50 |      45 |        05 |    02 |    03 |
|000700253            | Pledge            |    25 |      23 |        02 |    02 |  NULL |
|000700275            | Event Registration|    15 |      13 |        02 |    01 |    01 |
|000700226            | Donation          |    20 |      19 |        01 |    01 |  NULL |
----------------------------------------------------------------------------------------

Ok, so the tax rates may not make sense but this is all just hypothetical but somehow I’d like to figure out how within SQL queries to create the dynamic sub-query and it could add one or many tax_n’s to the main query depending on the number of taxes that are in the tbl_tax_type table.

Any pointers or ideas?

I hope my ASCII art came through all right for the results sets… It too forever to do them :rolleyes:

Thanks,
Andrew

Hi Andrew,

I would use a stored procedure. In this stored procedure, you find out what is the maximum number of tax_n in tbl_tax_type, then build the string of the query accordingly using the while loop, and then use execute yourquerystring to execute the query.
The other solution is to put the logic in the code calling the sql: first you call sql to get the maximum number of tax, then you build the query string, then you call sql with this query string.

Hope it helps
Vincent

Thanks for the reply Vincent.

I think either approach would be good but I’m unsure of how I can do it. I’ll tinker with this a little and see if I can make any progress. I’ll post back if I can push this forward.

Thanks again!
Andrew

OK, I have some progress to report… Maybe…

I should be able to add a dynamic subquery to my main query with a join for each query. I can do a SELECT id, tax FROM tax_type query and load it into an array. Then I can do a while loop and create a sting of subqueries as follows; one for each tax type in the system.


$subquery = "";
$sql = "SELECT id, tax FROM tax_type query";
$dao = CRM_Core_DAO::executeQuery($sql);
while( $dao->fetch( ) ) { 
    
    $subquery .= "LEFT JOIN (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE civi_tax_invoicing.tax_id = ".$dao->id.") AS ".$dao->tax." ON contribution_civireport.invoice_id = _invoicing_civireport.invoice_id;\
";

}

The results will look something like the following (note these are the more specific tables and table aliases I’m working with in reality):



        LEFT JOIN (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE civi_tax_invoicing.tax_id = 1) AS PST ON contribution_civireport.invoice_id = _invoicing_civireport.invoice_id COLLATE utf8_unicode_ci 
        LEFT JOIN (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE civi_tax_invoicing.tax_id = 2) AS GST ON contribution_civireport.invoice_id = _invoicing_civireport.invoice_id COLLATE utf8_unicode_ci 
        LEFT JOIN (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE civi_tax_invoicing.tax_id = 3) AS HST ON contribution_civireport.invoice_id = _invoicing_civireport.invoice_id COLLATE utf8_unicode_ci 


My final query is pretty ugly but here it is, warts and all:


SELECT SQL_CALC_FOUND_ROWS
 
    contact_civireport.sort_name as civicrm_contact_sort_name, 
    contact_civireport.id as civicrm_contact_id, 
    email_civireport.email as civicrm_email_email, 
    phone_civireport.phone as civicrm_phone_phone, 
    contacthonor.id as civicrm_contact_honor_id_honor, 
    _invoicing_civireport.pre_tax as civi_tax_invoicing_pre_tax, 
    sum(_invoicing_civireport.tax_charged) as civi_tax_invoicing_tax_charged_sum, 
    contribution_civireport.id as civicrm_contribution_contribution_id, 
    contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, 
    contribution_civireport.currency as civicrm_contribution_currency, 
    contribution_civireport.receive_date as civicrm_contribution_receive_date, 
    contribution_civireport.total_amount as civicrm_contribution_total_amount, 
    address_civireport.country_id as civicrm_address_country_id  

FROM

    civicrm_contact      contact_civireport 
    
        INNER JOIN civicrm_contribution contribution_civireport
            ON contact_civireport.id = contribution_civireport.contact_id AND contribution_civireport.is_test = 0
      
          LEFT JOIN civicrm_phone phone_civireport
            ON (contact_civireport.id = phone_civireport.contact_id) AND phone_civireport.is_primary = 1

        LEFT JOIN civicrm_address address_civireport
            ON contact_civireport.id = address_civireport.contact_id AND address_civireport.is_primary = 1

        LEFT JOIN civicrm_email email_civireport 
            ON contact_civireport.id = email_civireport.contact_id AND email_civireport.is_primary = 1

        LEFT JOIN civicrm_contact contacthonor
            ON contacthonor.id = contribution_civireport.honor_contact_id
        
        LEFT JOIN civi_tax_invoicing _invoicing_civireport 
            ON contribution_civireport.invoice_id = _invoicing_civireport.invoice_id COLLATE utf8_unicode_ci 

        LEFT JOIN (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE civi_tax_invoicing.tax_id = 1) AS PST ON contribution_civireport.invoice_id = _invoicing_civireport.invoice_id COLLATE utf8_unicode_ci 
        LEFT JOIN (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE civi_tax_invoicing.tax_id = 2) AS GST ON contribution_civireport.invoice_id = _invoicing_civireport.invoice_id COLLATE utf8_unicode_ci 
        LEFT JOIN (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE civi_tax_invoicing.tax_id = 3) AS HST ON contribution_civireport.invoice_id = _invoicing_civireport.invoice_id COLLATE utf8_unicode_ci 
        
WHERE (1) 
    AND ( contribution_civireport.receive_date >= 20140301000000 ) 
    AND ( contribution_civireport.receive_date <= 20140316235959 ) 
    AND contact_civireport.is_deleted = 0           

GROUP BY 
    contact_civireport.id, contribution_civireport.id   

ORDER BY contact_civireport.sort_name ASC  LIMIT 0, 50

So this query runs without introducing errors and doesn’t seem to introduce any performance penalties but I don’t know how to render the new fields for PST, GST and HST with my results.

Any thoughts?

Thanks,
Andrew

Well, I think I’m on the road forward and it’s definitely thanks to your reply Vincent. I owe you a debt of gratitude for pointing me in the right direction.

After reading my last post, I realized my subquery was in the wrong place entirely. Here is my modified full query:


SELECT SQL_CALC_FOUND_ROWS
 
    contact_civireport.sort_name as civicrm_contact_sort_name, 
    contact_civireport.id as civicrm_contact_id, 
    email_civireport.email as civicrm_email_email, 
    phone_civireport.phone as civicrm_phone_phone, 
    contacthonor.id as civicrm_contact_honor_id_honor, 
    _invoicing_civireport.pre_tax as civi_tax_invoicing_pre_tax, 
    # sum(_invoicing_civireport.tax_charged) as civi_tax_invoicing_tax_charged_sum, 
    contribution_civireport.id as civicrm_contribution_contribution_id, 
    contribution_civireport.financial_type_id as civicrm_contribution_financial_type_id, 
    contribution_civireport.currency as civicrm_contribution_currency, 
    contribution_civireport.receive_date as civicrm_contribution_receive_date, 
    contribution_civireport.total_amount as civicrm_contribution_total_amount, 
    address_civireport.country_id as civicrm_address_country_id,
    (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE contribution_civireport.invoice_id = civi_tax_invoicing.invoice_id  COLLATE utf8_unicode_ci AND civi_tax_invoicing.tax_id = 1) AS PST,
    (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE contribution_civireport.invoice_id = civi_tax_invoicing.invoice_id  COLLATE utf8_unicode_ci AND civi_tax_invoicing.tax_id = 2) AS GST,
    (SELECT civi_tax_invoicing.tax_charged FROM civi_tax_invoicing WHERE contribution_civireport.invoice_id = civi_tax_invoicing.invoice_id  COLLATE utf8_unicode_ci AND civi_tax_invoicing.tax_id = 3) AS HST

FROM

    civicrm_contact      contact_civireport 
    
        INNER JOIN civicrm_contribution contribution_civireport
            ON contact_civireport.id = contribution_civireport.contact_id AND contribution_civireport.is_test = 0
      
          LEFT JOIN civicrm_phone phone_civireport
            ON (contact_civireport.id = phone_civireport.contact_id) AND phone_civireport.is_primary = 1

        LEFT JOIN civicrm_address address_civireport
            ON contact_civireport.id = address_civireport.contact_id AND address_civireport.is_primary = 1

        LEFT JOIN civicrm_email email_civireport 
            ON contact_civireport.id = email_civireport.contact_id AND email_civireport.is_primary = 1

        LEFT JOIN civicrm_contact contacthonor
            ON contacthonor.id = contribution_civireport.honor_contact_id
        
        LEFT JOIN civi_tax_invoicing _invoicing_civireport 
            ON contribution_civireport.invoice_id = _invoicing_civireport.invoice_id COLLATE utf8_unicode_ci 
    
WHERE (1) 
    AND ( contribution_civireport.receive_date >= 20140301000000 ) 
    AND ( contribution_civireport.receive_date <= 20140316235959 ) 
    AND contact_civireport.is_deleted = 0           

GROUP BY 
    contact_civireport.id, contribution_civireport.id   

ORDER BY contact_civireport.sort_name ASC  LIMIT 0, 50


Note, I had to remark out my SUM of taxes because it seemed to keep summing through the sub queries and the total tax amounts were absolutely bizarre numbers as a result. I will try to do something with adding the taxes from my sub queries to get around that unless you have any pointers.

Thanks again,
Andrew