Prade jo Prade jo - 11 days ago 6
Java Question

join in table for multiple column

i have 2 tables [ table1, table2 ]

Table 1
+----+------------+
| id | name |
+--+--------------+
| 2 | Film |
| 1 | Music |
| 3 | OTHER |
| 5 | Sports |
| 4 | TV Content |
+----+------------+

Table 2
+----+-----+-----+
| id | id1 | id2 |
+----+-----+-----+
| 2 | 1 | 1 |
| 1 | 1 | 1 |
| 3 | 2 | 1 |
| 5 | 3 | 1 |
| 4 | 4 | 1 |
+----+-----+-----+


and my desired output is

+------------+------------+----------+
| id_name | id1_name | id2_name |
+------------+------------+----------+
| film | Music | Music |
| music | Music | Music | Required Output
| other | Film | Music |
| sports | OTHER | Music |
| TV Content | TV Content | Music |
+------------+------------+----------+


please suggest me how can i do this ;

Answer

As you have three different foreign keys, you need to do 3 different joins with the same table:

SELECT table1.name AS 'id_name',
       first.name AS 'id1_name',
       second.name AS 'id2_name'
FROM table2
    JOIN table1 ON
        table1.id = table2.id
    JOIN table1 first ON
        first.id = table2.id1
    JOIN table1 second ON
        second.id = table2.id2
Comments