CrazyMerlin
09-18-2008, 03:14 PM
Hey guys!
I have a 3 table inner join query that I want to convert to a sub-query query.
I have a main table that returns data about a media item. The item has tags associated with it, which are stored in a separate table, but to get those tags a third table is needed to associate the tag with the media item as there are different types of media items.
Inner Join Version:
SELECT `name`, `description`, `views`, `path`, `file_type`, (`total` / `votes`) AS `rating`, `tag`
FROM `vg_mdia_files`
INNER JOIN `vg_tags_taggers` ON (`file_idx` = `idx`)
INNER JOIN `vg_tags_tag` ON (`vg_tags_taggers`.`tag_idx` = `vg_tags_tag`.`tag_idx`)
WHERE `comp_id` = 'mdia' AND `file_idx` = 6
Subquery Version:
SELECT `name`, `description`, `views`, `path`, `file_type`, (`total` / `votes`) AS `rating`,
(SELECT `tag` FROM `vg_tags_tag` WHERE `tag_idx` IN (SELECT `tag_idx` FROM `vg_tags_taggers` WHERE `comp_id` = 'mdia' AND `idx` = 6)) AS `tags`
FROM `vg_mdia_files`
WHERE `file_idx` = 6
Now the inner join version will return 3 rows, but there is obviously common data so to eliminate this what I want to do is concat the data returned in the subquery version to a single row where the `tag` row is a string containing the tags returned from the outer most subquery.
Currently I get an error that the subquery returns more than one row, which I understand, so I need to get that data into a string.
Returning common data on 3 rows is fine, but once an item has a lot of associated tags it becomes a complete waste of resources.
Any help is greatly appreciated.
I have a 3 table inner join query that I want to convert to a sub-query query.
I have a main table that returns data about a media item. The item has tags associated with it, which are stored in a separate table, but to get those tags a third table is needed to associate the tag with the media item as there are different types of media items.
Inner Join Version:
SELECT `name`, `description`, `views`, `path`, `file_type`, (`total` / `votes`) AS `rating`, `tag`
FROM `vg_mdia_files`
INNER JOIN `vg_tags_taggers` ON (`file_idx` = `idx`)
INNER JOIN `vg_tags_tag` ON (`vg_tags_taggers`.`tag_idx` = `vg_tags_tag`.`tag_idx`)
WHERE `comp_id` = 'mdia' AND `file_idx` = 6
Subquery Version:
SELECT `name`, `description`, `views`, `path`, `file_type`, (`total` / `votes`) AS `rating`,
(SELECT `tag` FROM `vg_tags_tag` WHERE `tag_idx` IN (SELECT `tag_idx` FROM `vg_tags_taggers` WHERE `comp_id` = 'mdia' AND `idx` = 6)) AS `tags`
FROM `vg_mdia_files`
WHERE `file_idx` = 6
Now the inner join version will return 3 rows, but there is obviously common data so to eliminate this what I want to do is concat the data returned in the subquery version to a single row where the `tag` row is a string containing the tags returned from the outer most subquery.
Currently I get an error that the subquery returns more than one row, which I understand, so I need to get that data into a string.
Returning common data on 3 rows is fine, but once an item has a lot of associated tags it becomes a complete waste of resources.
Any help is greatly appreciated.