Mysql joining three tables getting to a single table

Question

I have three tables. parent, student, student_parent'

parent
p_id
p_name

student
s_id
s_name

student_parent
s_id
p_id

I want to form a new table for a reporting functionality, in the following format.

s_id | s_name | p_name_1 | p_name_2

Since two records are created for one student such as mother and father, i want to get both the records to a single table with one s_id. Currently i have a table like this,

p_id |   s_id | s_name | p_name
 01  |    01  |   sam  |   jack
 02  |    01  |   sam  |   jill

I want the table to be in the following structure.

s_id | s_name | p_name_1 | p_name_2
 01  |   sam  |   jack   |    jill

I have searched but could not find a proper solution.

Here is my curremt sql statement:

SELECT s.student_id, s.first_name, s.last_name, c.first_name
FROM fsms_student s, fsms_student_parent_guardian b, fsms_parent_guardian c
WHERE b.student_id = s.student_id AND b.parent_guardian_id = c.parent_guardian_id

I would be very much obliged if someone could provide me with a solution. Thank you.

rather show the statements with which you created the tables (foreign keys) — , Oct 23 '14 at 08:49
foreign keys for the first table i have are both p_id and s_id. In the new format i only need the s_id with the both names of the parent in the same row — Lahiru Tjay, Oct 23 '14 at 08:51
This wouldn't be a *table* as such but rather a *view*, or simply the result of a query. Now, how do you decide which parent is first? And look carefully at explicit join syntax... and... be careful and consistent when naming columns - it can get confusing for the rest of us. — Strawberry, Oct 23 '14 at 09:28
Yes this is not a table, i just need the selected data for reporting functionality. Can you provide me with a solution — Lahiru Tjay, Oct 23 '14 at 10:00
See this [answer](http://stackoverflow.com/a/1067452/1374307) for combining rows. — Rohith, Oct 23 '14 at 11:01
Group by `s.student_id` and `max(c.first_name)` as p_name_1 and `min(c.first_name)` as p_name_2. — Jaugar Chang, Oct 23 '14 at 11:19
possible duplicate of [Combine Multiple child rows into one row MYSQL](http://stackoverflow.com/questions/1067428/combine-multiple-child-rows-into-one-row-mysql) — Jaugar Chang, Oct 23 '14 at 11:21

Benvorth · Answer 1 · 2014-10-24T14:44:39.930

0

Why, you already have your solution:

CREATE TABLE newTable
SELECT 
    s.student_id as s_id, 
    concat(s.first_name, " ",s.last_name) as s_name, 
    count(distinct b.parent_guardian_id) as parentCount, 
    GROUP_CONCAT(p_name)
FROM 
    fsms_student s, fsms_student_parent_guardian b, fsms_parent_guardian c
WHERE 
    s.student_id = b.student_id  
    AND b.parent_guardian_id = c.parent_guardian_id 
GROUP BY 1,2

edited Oct 24 '14 at 14:44

answered Oct 23 '14 at 18:51

Benvorth

7,416
8
49
70

nope. the last name is the same persons last name. What i want is the name of the next person which corresponds to the stuent id – Lahiru Tjay Oct 24 '14 at 04:06
Ok, now I see what you want. See updated query above. You cant split all parent names in several cols but you can use `GROUP_CONCAT` to concat the parent's name in one colum – Benvorth Oct 24 '14 at 14:45

Mysql joining three tables getting to a single table

1 Answers1