Sql query to select data from a table with a complex structure

Question

I have a table with below structure.

I have no control over table changes.

It has three columns: student_name, student_id, name_id

Now the student’s name can be any number of words. Exactly one word will appear in one row. Based on the number of words, id_name will be entered, and student_id will be repeated.

The structure should be something like:

say name1: Ram Lakshman Prasad Sharma

and name2: Pandit Gangadhar Vidyadhar Mayadhar Omkarnath Shastri

So the table will look like this:

student_name | student_id | name_id ------------------------------------------------- Ram 1 1 Laxman 1 2 Prasad 1 3 Sharma 1 4 Pandit 2 1 Gangadhar 2 2 Vidyadhar 2 3 Mayadhar 2 4 Omkarnath 2 5 Shastri 2 6 

I hope I clearly explained the structure.

Now I want to write a query to read only the first four names for each student. However, if the number of names is less than four, an empty line must go, and if it is more than four, the first four must go, and the rest are simply ignored.

I need to put it in only one select request, since this request will be passed in the spring batch program. But I don't get how to quote the table of the column nameid to accept the first four name identifiers for each student.

How to create this sql for DB2 v8 database ??

Thank you for reading.

Answer 1

improved version inspired by Amit - if you need all 4 names in 1 column :)

  select t1.student_name || coalesce(' ' || t2.student_name, '') || coalesce(' ' || t3.student_name, '') || coalesce(' ' || t4.student_name, '') as "first 4 names" from mytable t1 left join mytable t2 on t1.student_id = t2.student_id and t2.name_id = 2 left join mytable t3 on t1.student_id = t3.student_id and t3.name_id = 3 left join mytable t4 on t1.student_id = t4.student_id and t4.name_id = 4 where t1.name_id = 1 

Answer 2

First, I do not have DB2, so syntax changes are possible

Try to execute

 select t1.student_id, ifnull(t2.names, ' ') from (select distinct(student_id) as student_id from tab ) as t1 left outer join ( select tab1.student_id, ifnull(concat(tab1.student_name, ' ', tab2.student_name, ' ', tab3.student_name, ' ', tab4.student_name),'') as names from (select * from tab where name_id = '1') tab1 inner join (select * from tab where name_id = '2') tab2 on tab1.student_id = tab2.student_id inner join (select * from tab where name_id = '3') tab3 on tab1.student_id = tab3.student_id inner join (select * from tab where name_id = '4') tab4 on tab1.student_id = tab4.student_id ) as t2 on t1.student_id = t2.student_id 

I worked on the fact that your name_id is a character. Also keep in mind that I wrote this query for MySQL, and DB2 may have a different syntax