==========================================
article_id | author | category
==========================================
10 | Tom | Ethics
10 | Tom | Social
11 | Dave | Sports
12 | Adam | Entertainment
12 | Adam | Jokes
12 | Adam | Amusing
13 | Peter | Health
==========================================
Actually I need to eleminate those duplicated rows and combine their categories to produce such result like this :
If you are using MySQL V. 4.1 or above, (I think) you can use subqueries for retrieveing this. Sample code (might need editing, havn't tested this myself):
SELECT distinct
Table_1.article_id,
Table_1.author,
(select distinct Table_2.category from Table_2 where Table_1.article_id = Table_2.article_id) as 'category'
FROM
Table_1
ORDER BY
Table_1.article_id;
There are other ways i guess, but i can only think of creating temporary tables and things like that, and it just doesn't sound right enough to give examples on that. Try the above first.
Actually , the server which i'm dealing with still using MySQL 3.23
your proposal of using subqueries looks great and i heared that 4.1 is also able to do the same with GROUP_CONCAT.
Anyway , if there is such way rather than having a temporary table that would be wonderful
Very welcome. Note that 4.0.18 at writing time is the latest 'stable' build, so do not expect to see it in use on hosting companies.
Just mentioning it as it's very annoying to test something in an home development enviroment, using the latest tools (MySQL5, PHP5) just to realise that the remote host you might upload the plages to can't handle it...