Fuzzy Orange
New Member
how can I use Zend_Db_Select to directly select from a subquery (derived table)?See, I have 5 tables with the same structure, I want to get all rows from them, merge them and remove the duplicates. I am using UNION which removes duplicates automaticly. The problem is that I add a static column to each table before, so there is one column which is different => duplicatation occures.Here is my query so far:\[code\]SELECT `news_main`.*, 'main' as `category` FROM `news_main` UNION SELECT `news_politics`.*, 'politics' as `category` FROM `news_politics` UNION SELECT `news_society`.*, 'society' as `category` FROM `news_society` UNION SELECT `news_world`.*, 'world' as `category` FROM `news_world` UNION SELECT `news_business`.*, 'business' as `category` FROM `news_business` ORDER BY `date` DESC LIMIT 8\[/code\]See how I add static values to the new column \[code\]category\[/code\]? Now everything else is the same (there are duplicate rows), but since they are from different categories, UNION can't remove them.So I thought I could \[code\]SELECT\[/code\] all rows from this sub-query and group them to remove duplicates, like this:\[code\]SELECT * FROM ( SELECT `news_main`.*, 'main' as `category` FROM `news_main` UNION SELECT `news_politics`.*, 'politics' as `category` FROM `news_politics` UNION SELECT `news_society`.*, 'society' as `category` FROM `news_society` UNION SELECT `news_world`.*, 'world' as `category` FROM `news_world` UNION SELECT `news_business`.*, 'business' as `category` FROM `news_business` ORDER BY `date` DESC LIMIT 8) as subtable GROUP BY `source` ORDER BY `date` DESC\[/code\]I did run this in MySQL and it works perfectly.. the only problem is....HOW THE F**K do I execute this using Zend_Db_Select's fancy functions?Thanks in advance!