Combining two tables MYSQL

["Mi-ul" <webforumsuser@xxxxxxxxxxxxxx>]

Hi

 I'm struggling here. This isn't as straightforward as it looks at first glance.

 I've got two tables - one for assets and one for actions performed on those 
assets. I'm trying to create a report that shows each asset and then the most 
recent action of type V and also the most recent action of type E.

 (actually the asset table is 6 tables linked together to transform ID codes to 
descriptive text) 
 (Note any typos below are from simplifying the actual code the page does work 
but very slowly - as you'd expect)

 table 1: asset
 assetID - primary key e.g. XX12345
 assetDescription
 assetLocation

 table 2: assetAction
 assetActionID - primary key, autoinc  (using this for most recent)
 assetActionAssetID - asset table's assetID
 assetActionType - e.g. E, V or other things I'm not interested in at this point
 assetActionResult

 Currently I'm doing it a very horrible way but It got it to where I needed it 
to in the right timescale:

 <cfquery name="assetList">SELECT * FROM asset ORDER BY assetID  </cfquery>

 <cfoutput query="assetList">
   <tr>#assetList  details#</tr>
   <cfquery = "assetActionE" maxRows = "1"> 
     SELECT * FROM assetAction  
     WHERE assetActionAssetID = #asset.assetID# AND assetAction = 'E'
     ORDER BY assetActionID desc
   </cfquery>
   
   <tr>#assetActionE details#</tr>

   <cfquery = "assetActionV" maxRows = "1"> 
     SELECT * FROM assetAction  
     WHERE assetActionAssetID = #asset.assetID# AND assetAction = 'V'
     ORDER BY assetActionID desc
   </cfquery>
   
   <tr>#assetActionV details#</tr>
 </cfoutput>


 My problem is combining the queries without creating a working table that has 
each asset listed for each different action. I can't find a way currently to 
combine the asset with just the two records from the assetAction table.


 Please let me know which bits of this I haven't explained properly and I look 
forward to any suggested solutions

 Michael