IT Community - Software Programming, Web Development and Technical Support

multiple group_concat problem

This is a discussion on multiple group_concat problem within the Database Support forums, part of the Web Development category; I have a set of three tables with two "linking" tables (appended below). I can use group_concat once ...


Go Back   IT Community - Software Programming, Web Development and Technical Support > Web Development > Database Support

Reload this Page multiple group_concat problem

Register FAQ Members List Calendar Mark Forums Read

Reply
 
LinkBack (1) Thread Tools Display Modes
  1 links from elsewhere to this Post. Click to view. #1 (permalink)  
Old 11-29-2007, 01:00 PM
fncll fncll is offline
D-Web Trainee
  
Join Date: Nov 2007
Posts: 1
fncll is on a distinguished road
Default multiple group_concat problem
I have a set of three tables with two "linking" tables (appended below). I can use group_concat once successfully on either of the tables:

Code:
mysql>  select community_agency.cid, communities.community,
    ->  group_concat(agencies.agency order by agency separator ',') as agencies
    ->  from  community_agency, communities, agencies
    ->  where community_agency.cid = communities.id
    ->  and community_agency.aid = agencies.id
    ->  group by communities.community;
+------+-----------+-----------------+
| cid  | community | agencies        |
+------+-----------+-----------------+
|    1 | seward    | NGDC,NOAA       |
|    2 | valdez    | AEIC,NGDC,USDHS |
+------+-----------+-----------------+
Code:
mysql>  select communities.community,
    ->  group_concat(contacts.first order by first separator ',') as contacts
    ->  from  community_contact, communities, contacts
    ->  where community_contact.cid = communities.id
    ->  and community_contact.conid = contacts.id
    ->  group by communities.community;
+-----------+------------+
| community | contacts   |
+-----------+------------+
| seward    | Homer,John |
| valdez    | Chris,John |
+-----------+------------+
What I want is to group_concat twice and get a result like:

Code:
+------+-----------+-----------------+----------- +
| cid  | community | agencies        |  contacts  |
+------+-----------+-----------------+----------- +
|    1 | seward    | NGDC,NOAA       | Homer,John |
|    2 | valdez    | AEIC,NGDC,USDHS | Chris,John |
+------+-----------+-----------------+------------+
Can it be done? DISTINCT doesn't seem to help. The closest I could get was:

Code:
mysql> select communities.community,   group_concat(distinct agencies.agency order by agency separator ',') as agencies,  group_concat(distinct contacts.first order by first separator ',') as contacts  from  community_contact, communities, contacts, agencies  where community_contact.cid = communities.id   and community_contact.conid = contacts.id   group by communities.community;
+-----------+----------------------+------------+
| community | agencies             | contacts   |
+-----------+----------------------+------------+
| seward    | AEIC,NGDC,NOAA,USDHS | Homer,John |
| valdez    | AEIC,NGDC,NOAA,USDHS | Chris,John |
+-----------+----------------------+------------+
But as you can see the agencies are duplicated...

***TABLES***

Code:
mysql> select * from communities;
+----+-----------+
| id | community |
+----+-----------+
|  1 | seward    |
|  2 | valdez    |
+----+-----------+

mysql> select * from agencies;
+----+--------+
| id | agency |
+----+--------+
|  1 | NGDC   |
|  2 | NOAA   |
|  3 | AEIC   |
|  4 | USDHS  |
+----+--------+

mysql> select * from contacts;
+----+---------+-------+
| id | last    | first |
+----+---------+-------+
|  1 | Lott    | Chris |
|  2 | Doe     | John  |
|  3 | Simpson | Homer |
+----+---------+-------+

mysql> select * from community_agency;
+------+------+
| cid  | aid  |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    1 |
|    2 |    3 |
|    2 |    4 |
+------+------+

mysql> select * from community_contact;
+------+-------+
| cid  | conid |
+------+-------+
|    1 |     2 |
|    1 |     3 |
|    2 |     1 |
|    2 |     2 |
|    3 |     1 |
+------+-------+
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Sponsored Links
  #2 (permalink)  
Old 11-30-2007, 12:08 AM
Murali Murali is offline
D-Web Master
  
Join Date: Feb 2007
Location: India-Chennai.
Posts: 386
Murali is on a distinguished road
Send a message via AIM to Murali
Default Re: multiple group_concat problem
Hi,
Quote:
select communities.community, group_concat(distinct agencies.agency order by agency separator ',') as agencies, group_concat(distinct contacts.first order by first separator ',') as contacts from community_contact, communities, contacts, agencies where community_contact.cid = communities.id and community_contact.conid = contacts.id group by communities.community
U have used four tables in your query and it seems that u have missed out a relationship of one table.

Check it out.

Quote:
Originally Posted by fncll View Post
I have a set of three tables with two "linking" tables (appended below). I can use group_concat once successfully on either of the tables:

Code:
mysql>  select community_agency.cid, communities.community,
    ->  group_concat(agencies.agency order by agency separator ',') as agencies
    ->  from  community_agency, communities, agencies
    ->  where community_agency.cid = communities.id
    ->  and community_agency.aid = agencies.id
    ->  group by communities.community;
+------+-----------+-----------------+
| cid  | community | agencies        |
+------+-----------+-----------------+
|    1 | seward    | NGDC,NOAA       |
|    2 | valdez    | AEIC,NGDC,USDHS |
+------+-----------+-----------------+
Code:
mysql>  select communities.community,
    ->  group_concat(contacts.first order by first separator ',') as contacts
    ->  from  community_contact, communities, contacts
    ->  where community_contact.cid = communities.id
    ->  and community_contact.conid = contacts.id
    ->  group by communities.community;
+-----------+------------+
| community | contacts   |
+-----------+------------+
| seward    | Homer,John |
| valdez    | Chris,John |
+-----------+------------+
What I want is to group_concat twice and get a result like:

Code:
+------+-----------+-----------------+----------- +
| cid  | community | agencies        |  contacts  |
+------+-----------+-----------------+----------- +
|    1 | seward    | NGDC,NOAA       | Homer,John |
|    2 | valdez    | AEIC,NGDC,USDHS | Chris,John |
+------+-----------+-----------------+------------+
Can it be done? DISTINCT doesn't seem to help. The closest I could get was:

Code:
mysql> select communities.community,   group_concat(distinct agencies.agency order by agency separator ',') as agencies,  group_concat(distinct contacts.first order by first separator ',') as contacts  from  community_contact, communities, contacts, agencies  where community_contact.cid = communities.id   and community_contact.conid = contacts.id   group by communities.community;
+-----------+----------------------+------------+
| community | agencies             | contacts   |
+-----------+----------------------+------------+
| seward    | AEIC,NGDC,NOAA,USDHS | Homer,John |
| valdez    | AEIC,NGDC,NOAA,USDHS | Chris,John |
+-----------+----------------------+------------+
But as you can see the agencies are duplicated...

***TABLES***

Code:
mysql> select * from communities;
+----+-----------+
| id | community |
+----+-----------+
|  1 | seward    |
|  2 | valdez    |
+----+-----------+

mysql> select * from agencies;
+----+--------+
| id | agency |
+----+--------+
|  1 | NGDC   |
|  2 | NOAA   |
|  3 | AEIC   |
|  4 | USDHS  |
+----+--------+

mysql> select * from contacts;
+----+---------+-------+
| id | last    | first |
+----+---------+-------+
|  1 | Lott    | Chris |
|  2 | Doe     | John  |
|  3 | Simpson | Homer |
+----+---------+-------+

mysql> select * from community_agency;
+------+------+
| cid  | aid  |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    2 |    1 |
|    2 |    3 |
|    2 |    4 |
+------+------+

mysql> select * from community_contact;
+------+-------+
| cid  | conid |
+------+-------+
|    1 |     2 |
|    1 |     3 |
|    2 |     1 |
|    2 |     2 |
|    3 |     1 |
+------+-------+
__________________
-Murali..
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #3 (permalink)  
Old 11-30-2007, 12:11 AM
write2ashokkumar write2ashokkumar is offline
D-Web Programmer
  
Join Date: Mar 2007
Posts: 85
write2ashokkumar is on a distinguished road
Default Re: multiple group_concat problem
Yes, Murali you are correct ....
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #4 (permalink)  
Old 11-30-2007, 12:22 AM
priyan priyan is offline
D-Web Sr.Programmer
  
Join Date: Mar 2007
Posts: 133
priyan is on a distinguished road
Default Re: multiple group_concat problem
Hi,

Check out this query, you will get the expected result.

Quote:
SELECT communities.community,
group_concat(distinct agencies.agency order by agency separator ',') as agencies,
group_concat(distinct contacts.first order by first separator ',') as contacts
FROM community_contact, communities, contacts, agencies,community_agency
WHERE community_contact.cid = communities.id
and community_contact.conid = contacts.id
and community_agency.cid = communities.id
and community_agency.aid = agencies.id
group by communities.community

Result set

Code:

+-----------+-----------------+------------+
| community | agencies        | contacts   |
+-----------+-----------------+------------+
| seward    | NGDC,NOAA       | Homer,John |
| valdez    | AEIC,NGDC,USDHS | Chris,John |
+-----------+-----------------+------------+
__________________
Keep smiling...
Last edited by priyan : 11-30-2007 at 12:25 AM.
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
  #5 (permalink)  
Old 11-30-2007, 01:06 AM
prasath prasath is offline
D-Web Sr.Programmer
  
Join Date: Jul 2007
Location: Chennai
Posts: 173
prasath is on a distinguished road
Thumbs up Re: multiple group_concat problem
Hi,
here i diplay ur expecting result


/*mysql> select * from communities;
+----+-----------+
| id | community |
+----+-----------+
| 1 | seward |
| 2 | valdez |
+----+-----------+*/
CREATE TABLE communities
(id INTEGER,
community VARCHAR(50)
);
INSERT INTO communities VALUES(1,'seward');
INSERT INTO communities VALUES(2,'valdez');
COMMIT;
/*mysql> select * from agencies;
+----+--------+
| id | agency |
+----+--------+
| 1 | NGDC |
| 2 | NOAA |
| 3 | AEIC |
| 4 | USDHS |
+----+--------+*/
CREATE TABLE agencies
(id INTEGER,
agency VARCHAR(50)
);
INSERT INTO agencies VALUES(1,'NGDC');
INSERT INTO agencies VALUES(2,'NOAA ');
INSERT INTO agencies VALUES(3,'AEIC ');
INSERT INTO agencies VALUES(4,'USDHS');
COMMIT;
/*mysql> select * from contacts;
+----+---------+-------+
| id | last | first |
+----+---------+-------+
| 1 | Lott | Chris |
| 2 | Doe | John |
| 3 | Simpson | Homer |
+----+---------+-------+*/
CREATE TABLE contacts
(id INTEGER,
last VARCHAR(50),
first VARCHAR(50)
);
INSERT INTO contacts VALUES(1,'Lott','Chris');
INSERT INTO contacts VALUES(2,'Doe','John');
INSERT INTO contacts VALUES(3,' Simpson','Homer');
COMMIT;
/*mysql> select * from community_agency;
+------+------+
| cid | aid |
+------+------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
| 2 | 4 |
+------+------+*/
CREATE TABLE community_agency
(cid INTEGER,
aid INTEGER
);
INSERT INTO community_agency VALUES(1,1);
INSERT INTO community_agency VALUES(1,2);
INSERT INTO community_agency VALUES(2,1);
INSERT INTO community_agency VALUES(2,3);
INSERT INTO community_agency VALUES(2,4);
COMMIT;
/*mysql> select * from community_contact;
+------+-------+
| cid | conid |
+------+-------+
| 1 | 2 |
| 1 | 3 |
| 2 | 1 |
| 2 | 2 |
| 3 | 1 |
+------+-------+ */
CREATE TABLE community_contact
(cid INTEGER,
conid INTEGER
);
INSERT INTO community_contact VALUES(1,2);
INSERT INTO community_contact VALUES(1,3);
INSERT INTO community_contact VALUES(2,1);
INSERT INTO community_contact VALUES(2,2);
INSERT INTO community_contact VALUES(3,1);
COMMIT;



SELECT
com.id,com.community,
GROUP_CONCAT(DISTINCT agn.agency ORDER BY agency SEPARATOR ',') AS agencies,
GROUP_CONCAT(DISTINCT con.first ORDER BY first SEPARATOR ',' ) AS contacts
FROM
communities com,
agencies agn,
contacts con,
community_agency coma,
community_contact conc
WHERE
conc.cid=com.id AND
conc.conid=con.id AND
coma.aid=agn.id AND
coma.cid=com.id
GROUP BY community


Thanks,
Prasath.K
__________________
Prasath.K
Add Post to del.icio.usBookmark Post in TechnoratiFurl this Post!
Reply With Quote
Reply

« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

LinkBacks (?)
LinkBack to this Thread: http://www.discussweb.com/database-support/4604-multiple-group_concat-problem.html
Posted By For Type Date
multiple group_concat problem This thread Pingback 11-29-2007 06:56 PM

Similar Threads
Thread Thread Starter Forum Replies Last Post
group_concat in MySQL Jeyaseelansarc Database Support 43 09-29-2008 06:56 AM
Uploading Multiple Files in ASP.NET. H2o ASP and ASP.NET Programming 3 08-08-2007 06:39 AM
multiple file selection gattuso HTML, CSS and Javascript Coding Techniques 1 08-08-2007 05:36 AM
Problem in downloading multiple files as an archive. sureshbabu PHP Programming 0 07-20-2007 06:16 AM
Inherit multiple interfaces vigneshgets C# Programming 1 07-12-2007 04:34 AM


All times are GMT -7. The time now is 05:56 AM.

Contact Us - IT Community - Archive - Top

Copyright ©2004 - 2007, DiscussWeb. All Rights Reserved.

SEO by vBSEO 3.0.0