Query returning related assets
Posted
by GMo
on Stack Overflow
See other posts from Stack Overflow
or by GMo
Published on 2010-06-15T23:51:07Z
Indexed on
2010/06/16
0:02 UTC
Read the original article
Hit count: 223
mysql-query
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?
© Stack Overflow or respective owner