How to join in single row result with one row and multiple rows in another table?-CodePudding

I have 2 tables, tables 1 is tree structure.

Table 1:
ID      NAME    PARENTID
-------------------------
1       Book1       0
2       Page        1
3       Line1       2
4       Line2       2
5       Book2       0
6       Page1       5
7       Page2       5
8       Line1       6
9       Line2       6

Table 2:
ID      BOOK        PAGE        LINE
1         1           2           4     
2         5           7           9

I want to get all of rows in table 2 and show the Name of table 1 in one line

ID      BOOK        PAGE        LINE
1       Book1       Page        Line2       
2       Book2       Page2       Line2

How can I show the data from multiple rows and 1 row in single row not duplicate if I use simple select. Sorry for about the stupid problem. Thanks for your help.

CodePudding user response：

Use joins (tbl is second table):

   select t1.id, 
          t2.name book, 
          t3.name page, 
          t4.name line
    from tbl t1
    join treeTbl t2
      on t1.book = t2.id
    join treeTbl t3
       on t3.id=t1.page
    join treeTbl t4
       on t4.id=t1.line