title: SQL Query - referencing inner query
body: <p>I'm not an seasoned SQL writer, but can do generic selects and joins, but I have a more complicated SQL that I'm trying to write where I need to grab a inner result set and the do some joins based on it.</p>
  
  <p>My sub or inner query is as such:</p>
  
  <pre><code> ( 
   SELECT    a.item_code, 
                       Concat('$', Format(Sum(a.standard),2, 'tan_in')) AS `standard`, 
                       concat('$', format(sum(a.si),2, 'tan_in'))       AS `si`, 
                       concat('$', format(sum(a.tessco),2, 'tan_in'))   AS `tessco price list` 
   FROM      ( 
                             SELECT DISTINCT t.item_code, 
                                             t.standard, 
                                             t.si, 
                                             t.tessco 
                             FROM            `tabitem price` AS t1 
                             JOIN 
                                             ( 
                                                             SELECT DISTINCT p.item_code,IF(p.price_list = "Standard Selling", p.price_list_rate, 0)  as standard,
                                                                             IF(p.price_list = "SI",p.price_list_rate,0)                  AS si,
                                                                             IF(p.price_list = "Tessco Price List", p.price_list_rate, 0) AS tessco
                                                             FROM            `tabitem price` p 
                                                             INNER JOIN      `tabitem price` r 
                                                             ON              r.item_code = p.item_code
                                                             ORDER BY        item_code) t 
                             ON              t1.item_code = t.item_code) a 
   LEFT JOIN `tabitem` tabi 
   ON        tabi.NAME = a.item_code 
   GROUP BY  a.item_code) AS final
  </code></pre>
  
  <p>And outside of this I have:</p>
  
  <pre><code>SELECT
  tabCustomer.customer_name AS 'Customer::150', 
  CONCAT(FORMAT(`tabPricing Rule`.discount_percentage,2),'%%') AS 'Discount %%', 
  `tabPricing Rule`.item_group AS 'Discount Group',
  `tabPricing Rule`.title AS 'Discount Title',
  final.item_code AS 'Item::125', 
  tabItem.description AS 'Description::250',
  `tabItem Price`.price_list AS 'Price List::125',
  final.Standard AS 'List Price',
  final.SI AS 'SI Price',
  CONCAT('$', FORMAT(`tabItem Price`.price_list_rate,2)) AS 'Customer Price::125',
  IF(`tabPricing Rule`.item_group = "Top level", CONCAT('$', FORMAT(`tabItem Price`.price_list_rate * (1-(`tabPricing Rule`.discount_percentage/100)),2)), if(`tabPricing Rule`.item_group = tabItem.item_group, CONCAT('$', FORMAT(`tabItem Price`.price_list_rate * (1-(`tabPricing Rule`.discount_percentage/100)),2)), CONCAT('$', FORMAT(`tabItem Price`.price_list_rate,2)))) AS 'Discounted Price',
  tabItem.item_group AS 'Category::100',
  tabItem._user_tags AS 'User Tag::100'
  FROM final  
  </code></pre>
  
  <p>/* inner query goes here */</p>
  
  <pre><code>WHERE tabitem.disabled = '0' 
  AND  tabitem.item_group != 'Third Party Components' 
  AND  tabitem.item_group != 'Engineering Services' 
  AND  tabitem.item_group != 'Services' 
  ORDER BY tabitem.item_group, 
           tabitem._user_tags, 
          `tabitem price`.price_list_rate;
  </code></pre>
  
  <p>When I run this it says that 'final' table does not exist.  </p>
  
  <p>My question is how do i reference the inner result set?</p>
  
  <p>Thank you.</p>
  
  <p>Regards,</p>
  
  <p>JR</p>