473,324 Members | 2,548 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

combine multiple row results in one comma sep. field using join

Hi guys,

Currently I have student's management database project going on. For a page which "list all students" I would wanna do a
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tbl_students
However each student has 1 or more subjects he/she can take. So I would like to also display the subjects in the "list all students" page, in a comma-delimited form
ie: "Maths, English, Science"

In the case of joining the tables, i can simply join based on the student_id field, but that will give me more than 10 records if i had 10 students in the database. How will I go about joining and grouping them so that i have something like this:
Expand|Select|Wrap|Line Numbers
  1. id     name        subjects
  2. 1      John         English, Maths
  3. 2      Doe          Science, English
Many Thanks!
Mar 1 '08 #1
5 3214
ronverdonk
4,258 Expert 4TB
From you post I cannot make up if there is one table or if there are 2 tables involved. Please show the involved tables with their relevant columns.

Ronald
Mar 1 '08 #2
assuming it is not in any normalized form

Students Table {student_id, student_name etc..}
Subjects Table {subject_id, student_id, subject_name etc..}
Mar 1 '08 #3
ronverdonk
4,258 Expert 4TB
Not normalized at all! Have a try with this one
Expand|Select|Wrap|Line Numbers
  1. select student_name as Student_name, GROUP_CONCAT(subject_name) as Courses from students as s1 left join subjects as s2 on s1.student_id=s2.student_id group by student_name;
Ronald
Mar 1 '08 #4
wow thanks ronald. works like a charm..
i never knew there was this function in mysql ^^

Regards
Mar 1 '08 #5
ronverdonk
4,258 Expert 4TB
There are sooo many functions in MySQL, you'd be amazed. Anyway, glad I could help out. See you next time.

Ronald
Mar 1 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: Soefara | last post by:
Dear Sirs, I am experiencing strange results when trying to optimize a LEFT JOIN on 3 tables using MySQL. Given 3 tables A, B, C such as the following: create table A ( uniqueId int not...
7
by: Dr John Stockton | last post by:
What are the best ways of returning multiple results from a subroutine ? I've been using ... return } which is inelegant. I'm used to Pascal's procedure X(const A, B : integer; var C, D :...
2
by: Ringo Langly | last post by:
Hi all, I need to put multiple results in one field, but not sure how. Here's some sample code: select a.name, a.accountnum, a.ordernum, (select itemid from items where items_ordernum =...
4
by: musicloverlch | last post by:
I have a table with multiple records in it. I am being forced to combine multiple records into one record in order to be uploaded to a website, and I only get one record per client. How can I...
2
by: webfreak | last post by:
hello am a relative newbie to php. am using dreamweaver,php/mysql. i want to populate a mysql field using a multiple selection list menu. i have been to countless forums and so far nothing. can...
1
by: kababoom | last post by:
Is it possible to combine multiple sql queries into 1? or does anyone has any sites or reference on how to do this? Thks for any info :)
4
werks
by: werks | last post by:
Hello experts could someone tell me how can i combine multiple records into one value. Sample in MS Access: (Lastname & "" & "," & " " & Firstname) AS Name How can i do this in MySQL?..tnx in...
3
by: klbachrodt | last post by:
Hi all - I've been browsing this forum for a while now, looking for help, and finally decided to join and ask my question, since I'm not terribly good at writing SQL queries in Access. Here is my...
4
by: Brad Galinson | last post by:
I have a long sql query that, among other things, accesses multiple tables with an inner join and some left outer joins. I am expecting it to give a single set of results but when it finds more than...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.