Query returning related assets

Question

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.

Here are the table definitions:

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;

In the asset_links table there are the following rows:

INSERT INTO `Asset_links` (asset_id1, asset_id2) VALUES 
(1,3), (1,4), (2,10), (2,56);

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.

e.g. The query returning assets 1 and 2 would return :

• Asset 1 attributes

  • Asset 3 attributes
  • Asset 4 attributes

• Asset 2 attributes

  • Asset 10 attributes
  • Asset 56 attributes

What is the best way to write the query?

Answer 1

SELECT a1.*, a2.*
FROM Asset a1
LEFT OUTER JOIN (Asset_Links l JOIN Asset a2 ON l.asset_id2 = a2.id)
  ON (a1.id = l.asset_id1)
WHERE a1.id IN (1, 2)
  AND ...whatever conditions you want here...

I'm leaving placeholder text for your "any search criteria" because that's not a real question.

---

Re your comment: You can't get the result you're describing from a relational database in one query. An SQL query result must have the same columns on every row, and every row has values for those columns. You can't do what you describe in a single SQL query.

If you want a result set with super-rows and sub-rows, you have to use a non-relational database. Try CouchDB or MongoDB.