Query returning related assets
- by GMo
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:
Asset
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| source | varchar(255) | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| date_created | datetime | YES | | NULL | |
| date_embargo | datetime | YES | | NULL | |
| date_expires | datetime | YES | | NULL | |
| date_updated | datetime | YES | | NULL | |
| keywords | varchar(255) | YES | | NULL | |
| status | int(11) | YES | | NULL | |
| priority | int(11) | YES | | NULL | |
| fk_site | int(11) | YES | MUL | NULL | |
| resource_type | varchar(255) | YES | | NULL | |
| resource_id | int(11) | YES | | NULL | |
| fk_user | int(11) | YES | MUL | NULL | |
+---------------+--------------+------+-----+---------+----------------+
Asset_links
+-----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| asset_id1 | int(11) | YES | | NULL | |
| asset_id2 | int(11) | YES | | NULL | |
+-----------+---------+------+-----+---------+----------------+
In the asset_links table there are the following rows:
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?
