title: Query returning related assets
body: <p>I have 2 tables, one is an assets table which holds digital assets (e.g. article, images etc), the 2nd table is an asset_links table which maps 1-1 relationships between assets contained within the assets table.</p>
  
  <p>Here are the table definitions:</p>
  
  <pre><code>CREATE TABLE `asset` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `source` varchar(255) DEFAULT NULL,
    `title` varchar(255) DEFAULT NULL,
    `date_created` datetime DEFAULT NULL,
    `date_embargo` datetime DEFAULT NULL,
    `date_expires` datetime DEFAULT NULL,
    `date_updated` datetime DEFAULT NULL,
    `keywords` varchar(255) DEFAULT NULL,
    `status` int(11) DEFAULT NULL,
    `priority` int(11) DEFAULT NULL,
    `fk_site` int(11) DEFAULT NULL,
    `resource_type` varchar(255) DEFAULT NULL,
    `resource_id` int(11) DEFAULT NULL,
    `fk_user` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  
  CREATE TABLE `asset_links` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `asset_id1` int(11) DEFAULT NULL,
    `asset_id2` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB;
  </code></pre>
  
  <p>In the asset_links table there are the following rows:</p>
  
  <pre><code>INSERT INTO `Asset_links` (asset_id1, asset_id2) VALUES 
  (1,3), (1,4), (2,10), (2,56);
  </code></pre>
  
  <p>I am looking to write one query which will return all assets which satisfy any asset search criteria and within the same query return all of the linked asset data for linked assets for that asset.</p>
  
  <p>e.g. The query returning assets 1 and 2 would return :</p>
  
  <ul>
  <li><p>Asset 1 attributes</p>
  
  <ul>
  <li>Asset 3 attributes</li>
  <li>Asset 4 attributes</li>
  </ul></li>
  <li><p>Asset 2 attributes</p>
  
  <ul>
  <li>Asset 10 attributes</li>
  <li>Asset 56 attributes</li>
  </ul></li>
  </ul>
  
  <p>What is the best way to write the query?</p>