create a table based on other tables

Question

I have 4 different tables:

student: sid, name, gender 

grades: sid, grade

address: postcode, sid, distance_to_school

health_condition: sid, health_code

Now I want to make a new table that contains all of the columns that I mentioned above. I need to join them based on the sid. I tried to add columns separately but the code is really long. So is there another way to do this task?

Why don’t you just join them when you need to output data? So each time you can pick up just the columns you need — Lelio Faieta, May 28 '20 at 08:44
like A join B join C....? I'm new to SQL so I'm not really sure how can I do this — SadSalad, May 28 '20 at 08:46
Yep! Select fields you want from a join b on a.sid=b.sid join c on a.sid=c.sid is an idea — Lelio Faieta, May 28 '20 at 08:48
Why is question tagged with pgAdmin and MySQL? Are you using MySQL or PostgreSQL? — Nico Haase, May 28 '20 at 08:59

score 0 · Accepted Answer · answered May 28 '20 at 08:50

If you want to show the data from the fifth table that you want to create, I think with the query is better, so you don't need the fifth table. Just

"SELECT * FROM student s
INNER JOIN grades g
on s.sid = g.sid
INNER JOIN address a
on s.sid = a.sid
INNER JOIN health_condition h
on s.sid = h.sid
ORDER BY sid ASC";

score 0 · Answer 2 · answered May 28 '20 at 08:53

Please use below query will give you the exact result set.

SELECT t1.sid, t1.name, t1.gender , t2.grade,t3.postcode, t3.distance_to_school, 
t4.health_code AS 
student as t1 inner join grades t2 on t1.sid=t2.sid 
inner join address as t3 on t3.sid= t1.sid 
inner join health_condition as t4 on t4.sid=t1.sid

score 0 · Answer 3 · answered May 28 '20 at 08:57

0

Try this query

FROM student s
LEFT JOIN grades g on g.sid = s.sid
LEFT JOIN address a on a.sid = s.sid
LEFT JOIN health_condition h on h.sid = s.sid
WHERE s.sid = ;

answered May 28 '20 at 08:57

krr2020

23
1
7

create a table based on other tables

3 Answers3