Page 1 of 1

Getting a result from 2 joined tables

Posted: Sun Apr 18, 2004 6:59 pm
by mishal
Hi everybody ..

I have two tables and want to join them in a query to produce a summary report.

Code: Select all

Table_1
====================
article_id | author
====================
10	   | Tom
11	   | Dave
12	   | Adam
13	   | Peter
====================

Table_2
====================
article_id | category
====================
10	   | Ethics
10	   | Social
11	   | Sports
12	   | Entertainment
12	   | Jokes
12	   | Amusing
13	   | Health
====================
The Query is :

Code: Select all

SELECT Table_1.article_id, Table_1.author, Table_2.category 
FROM Table_1, Table_2 
WHERE Table_1.article_id=Table_2.article_id 
ORDER BY article_id;
The result will be :

Code: Select all

==========================================
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 :

Code: Select all

===============================================================
article_id | author	  | category
===============================================================
10			| Tom		  | Ethics, Social
11			| Dave		 | Sports
12			| Adam		 | Entertainment, Jokes, Amusing
13			| Peter		| Health
===============================================================
So any idea of how to do this in MySQL (without using PHP, Perl ..etc.) ?

Thanks

Mishal

Posted: Mon Apr 19, 2004 10:06 am
by JAM
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):

Code: Select all

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.

Hope it helped.

Posted: Mon Apr 19, 2004 3:29 pm
by mishal
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. :roll:

Anyway , if there is such way rather than having a temporary table that would be wonderful

JAM, Thank you very much ! :)

Posted: Mon Apr 19, 2004 3:45 pm
by JAM
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...