Select data from multiple tables using SQL-CodePudding

I have three tables as follows,

student table
{sid, sname}
e.g.
1 Billy
2 Bob
3 David


subject table
{subid, subname}

 e.g.
      15 Maths
      16 Science
      62 Art

grades table
{sid, subid, marks, grades}

e.g.
      1 15 69 B
      1 16 56 C
      1 62 23 W
      2 15 45 C
      2 16 99 A
      2 62 80 A
      3 15 20 W
      3 16 40 S
      3 62 71 B

I want to retrieve data as follows,

Billy Maths 69 B Science 56 C Art 23 W
Bob   Maths 45 C Science 99 A Art 80 A 
David Maths 20 W Science 40 S Art 71 B

Is this possible in SQL? If so, how? Please help.

CodePudding user response：

Yes, it is possible, you would need to use concatting functions and join. Your database of choice might have different functions to GROUP_CONCAT and CONCAT, but it should work in MariaDB and MySQL

Here is an example of how to do this.

SELECT student.sname, GROUP_CONCAT(CONCAT(subject.subname, ' ', grades.marks, ' ', grades.grades) SEPARATOR ' ')
FROM student
JOIN grades ON grades.sid = student.sid
JOIN subject ON subject.subid = grades.subid
GROUP BY student.sid

Here is an example of it running https://dbfiddle.uk/RtiHsEmQ

CodePudding user response：

Is it doable, yes it is. Should you do it in the style you showed, probably not. That is known as cross tab or pivoting. For your very specific case here is an ugly way of doing that:

select st.sname, 
       max(case when sb.subid = 15 then sb.subname end) as 'S1',
       max(case when sb.subid = 15 then g.Marks end) as 'M1',
       max(case when sb.subid = 15 then g.grades end) as 'G1',
       max(case when sb.subid = 16 then sb.subname end) as 'S2',
       max(case when sb.subid = 16 then g.Marks end) as 'M2',
       max(case when sb.subid = 16 then g.grades end) as 'G2',
       max(case when sb.subid = 62 then sb.subname end) as 'S3',
       max(case when sb.subid = 62 then g.Marks end) as 'M3',
       max(case when sb.subid = 62 then g.grades end) as 'G3'
  from grades g 
  inner join student st on st.sid = g.sid
  inner join subject sb on sb.subid = g.subid
  group by st.sid, st.sname
  order by st.sname;

I don't know what cross tab libraries may exist in mySQL to make it easier. Say in postgreSQL, you might use tablefunc.

DBFiddle demo

EDIT: Now that you removed the DBMS tag, it might be postgereSQL, then you could JSON functions to build that up even in case of unknown subjects beforehand (or using dynamic SQL is an option).