title: how combine innerjoin query with 2 LCASE + REPLACE in 1 query?
body: <p>I try build searching with using <code>LCASE()</code> but it's not working?
  this is part of source query, only this part have a problem error.</p>
  
  <pre><code>AND ((LCASE(SELECT REPLACE (pd.name," ","")) FROM product_description 
  WHERE LCASE (REPLACE (pd.name," ",""))) LIKE '%liondlc10%'            
       OR MATCH(pd.tag) AGAINST('liondlc10')           
     )
     OR LCASE((SELECT REPLACE (p.model," ","")) FROM product WHERE LCASE  (REPLACE (p.model," ",""))) = 'liondlc10'
  </code></pre>
  
  <p>For full query :</p>
  
  <pre><code>SELECT p.product_id,
        (SELECT AVG(rating) AS total        
           FROM review r1        
          WHERE r1.product_id = p.product_id        
            AND r1.status = '1'
          GROUP BY r1.product_id) AS rating
    FROM product p       
             LEFT JOIN  product_description pd 
                  ON (p.product_id = pd.product_id)
             LEFT JOIN product_to_store p2s 
                  ON (p.product_id = p2s.product_id)
   WHERE pd.language_id = '1'       
     AND p.status = '1'       
     AND p.date_available &lt;= NOW()       
     AND p2s.store_id = '0'   
  
  
     AND ((LCASE(SELECT REPLACE (pd.name," ","")) FROM product_description 
  WHERE LCASE (REPLACE (pd.name," ",""))) LIKE '%liondlc10%'            
       OR MATCH(pd.tag) AGAINST('liondlc10')         /* &lt;-- part error */   
     )
  
     OR LCASE((SELECT REPLACE (p.model," ","")) FROM product 
     WHERE LCASE  (REPLACE (p.model," ",""))) = 'liondlc10'    /* &lt;-- part error */
  
  
      OR LCASE(p.sku) = 'liondlc10'       
      OR LCASE(p.upc) = 'liondlc10'       
      OR LCASE(p.ean) = 'liondlc10'       
      OR LCASE(p.jan) = 'liondlc10'       
      OR LCASE(p.isbn) = 'liondlc10'       
      OR LCASE(p.mpn) = 'liondlc10'       
   GROUP BY p.product_id       
   ORDER BY p.sort_order ASC, LCASE(pd.name) ASC
   LIMIT 0, 15
  </code></pre>
  
  <p>when I try this query, <code>mysql</code> said</p>
  
  <p>1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT REPLACE (pd.name," ","")) FROM product_description
      WHERE LCASE (REP' at line 16</p>
  
  <p>I could use <code>LCASE()</code>, but how would I do this with the following?
  need help, already stack for 2 week :( .</p>