Error in coorelated query in mysql

Question

When i run Following Query everything works well but the subQuery inside LEFT JOIN Fails and

      SELECT inv.*,rc.*,po.*,cfd.*
        FROM invoice_lineitem as inv
        LEFT JOIN (
            SELECT rc1.* FROM rentalcode as rc1 WHERE rc1.assetCategory=  
             (SELECT AssetCategory FROM assetmaster WHERE  
               AssetID=invoice_lineitem.asset_typeID)
               AND invoice_lineitem.invoice_date between rc1.start_date   
               AND rc1.validity_date
        ) rc ON rc.email=inv.usernameID
        LEFT OUTER JOIN purchase_order as po ON (po.PO_ID=inv.poNumber)
        LEFT OUTER JOIN client_facility_data as cfd ON (cfd.email=inv.usernameID)
        WHERE inv.contractID='2/1AS/10'

I get Error as

1054 - Unknown column 'invoice_lineitem.asset_typeID' in 'where clause'

When i try the same query by just removing the all invoice_lineitem from SubQuery and placing it in WHERE(beside contractID) the query works but it acts like INNER JOIN Query i.e if Query Fails for any of the Where Clause i get No Data

      SELECT inv.*,rc.*,po.*,cfd.*
        FROM invoice_lineitem as inv
        LEFT JOIN (
            SELECT rc1.* FROM rentalcode as rc1  
        ) rc ON rc.email=inv.usernameID
        LEFT OUTER JOIN purchase_order as po ON (po.PO_ID=inv.poNumber)
        LEFT OUTER JOIN client_facility_data as cfd ON (cfd.email=inv.usernameID)
        WHERE inv.contractID='2/1AS/10' AND rc.assetCategory=(SELECT AssetCategory FROM assetmaster WHERE AssetID=inv.asset_typeID) 
     AND inv.invoice_date between rc.start_date and rc.validity_date

Answer 1

A subquery can't refer to tables in the outer query (and if it could, you would have to use the inv. alias).

Try this. I changed the subquery into another LEFT JOIN.

  SELECT inv.*,rc.*,po.*,cfd.*
    FROM invoice_lineitem as inv
    LEFT JOIN rentalcode AS rc1 
        ON rc.email = inv.usernameID 
            AND inv.nvoice_date between rc1.start_date AND rc1.validity_date
    LEFT JOIN assetmaster AS am 
        ON rc1.assetCategory = am.AssetCategory 
            AND inv.asset_typeID = am.AssetID
    LEFT OUTER JOIN purchase_order as po ON (po.PO_ID=inv.poNumber)
    LEFT OUTER JOIN client_facility_data as cfd ON (cfd.email=inv.usernameID)
    WHERE inv.contractID='2/1AS/10'