How to write this query
Posted by roger3333 (roger3333), 2 December 2002Hi, 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).
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:
Now I want generate table like this:
How shall I write the sql query?
Thanks a lot.
Posted by admin (Graham Ellis), 3 December 2002You 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 2002Thanks 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 ?
Posted by admin (Graham Ellis), 4 December 2002MySQL 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 2002Thank you for your advice,Graham
I will post the solution if I can do it using MySQL query.
PH: 01144 1225 708225 • FAX: 01144 1225 899360 • EMAIL: email@example.com • WEB: http://www.wellho.net • SKYPE: wellho