Getting a result from 2 joined tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mishal
Forum Newbie
Posts: 12
Joined: Sat Apr 10, 2004 4:23 pm

Getting a result from 2 joined tables

Post 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
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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.
mishal
Forum Newbie
Posts: 12
Joined: Sat Apr 10, 2004 4:23 pm

Post 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 ! :)
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post 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...
Post Reply