Training, Open Source computer languages
PerlPHPPythonMySQLApache / TomcatTclRubyJavaC and C++LinuxCSS 
Search for:
Home Accessibility Courses Diary The Mouth Forum Resources Site Map About Us Contact
How to write this query

Posted by roger3333 (roger3333), 2 December 2002
Hi, Could anyone tell me how to write the query to combine all the values of a column together if they have the same value in another column (similar to group by operation).
e.g:
Say I have a table (name_subject) with two columns: Name, subject
The two columns together form the primary key.
A sample data in the table will sth. like this:

Name   Subject
Jackie Maths
Jackie Physics
Jackie Chemistry
Mary   Maths
Mary   Physics
Mary   Chemistry

Now I want generate table like this:

Name   AllSubjects
Jackie Maths,Physics,Chemistry
Mary   Maths,Physics,Chemistry

How shall I write the sql query?
Thanks a lot.

Roger

Posted by admin (Graham Ellis), 3 December 2002
You can get all the records for Mary followed by all the records for Jackie (etc) using
      SELECT * FROM subjects GROUP BY Name

Personally, I would then combine the subjects within my client as I step through the rows returned one by one, and output the results in a format under the control of my program in PHP, Perl or whatever.

I notice that you say "I want generate table" ... with the output. You really should not write a new table containing the combined data, as it goes against the principles of database design - as soon as you start duplicating the same data in two different formats, you have a maintenance nighmare as you try to keep the two in step.

Posted by roger3333 (roger3333), 3 December 2002
Thanks for you reply, Graham.  
Sorry for my vague expression.   What I mean is not to physically create another table in the database, but to create a temporary result set ( in a table form) for the application.
I agree that your suggestion is one possible alternative.
But I hope, if possible, the result set could be generated at the database level, rather than at application level. (so that I don't have to go through each row to do some operations in the application)
Do you have any idea how to write the SQL query to generate the result set ?

Regards,

Roger

Posted by admin (Graham Ellis), 4 December 2002
MySQL does have a CONCAT function to join strings together, but it's going to be more difficult with what you're wanting to do to select a whole series of values from one column where the values in another column are equal to actually feed into the CONCAT.  I'm not an Oracle DBA / Programmer, but I believe that this is the sort of thing you would do under the PL section of PL/SQL and not under the SQL which is really all MySQL has (yes, there's good stuff adding in at release 4 but the production release is still 3.23.53 and I would stick with that for production systems   )

You've given a very simple example of a table to manipulate - one of the reasons that I'm so reluctant to push MySQL to try to achieve what you want is there's a question in the back of my mind  ... "and what are we going to do with other fields on the lines that we connect?".    Using PL,  I think you have the tools to do whatever with other fields;  with Perl, PHP, Java, etc you have the tools too.   Going to the limits within the SQL of MySQL, doing data manipulation with a lean database engine, I think you might find yourself being scuppered as you extend the code.

All this "IMHO" (in my humble opinion);  please do post if you manage to achieve the task in pure (My)SQL - you might persuade me that you have a good solution but at present I think it's "horses for courses" and the horse for your manipulation should be  a programming language and not a database access language.

Posted by roger3333 (roger3333), 4 December 2002
Thank you for your advice,Graham
I will post the solution if I can do it using MySQL query.



This page is a thread posted to the opentalk forum at www.opentalk.org.uk and archived here for reference. To jump to the archive index please follow this link.

You can Add a comment or ranking to this page

© WELL HOUSE CONSULTANTS LTD., 2014: Well House Manor • 48 Spa Road • Melksham, Wiltshire • United Kingdom • SN12 7NY
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: info@wellho.net • WEB: http://www.wellho.net • SKYPE: wellho