sqldatabasejoinselect

select multiple cells inner join one table

please help me how to write query for these tables:

table_drive:

id model
1 HDD 1TB
2 SSD 1TB
3 SSD 500
4 other

table_system:

name drive_1 drive_2
system1 1 2
system2 3 1

select query output:

name drive_1 drive_2
system1 HDD 1TB SSD 1TB
system2 SSD 500 HDD 1TB

thanks;

i tried to join, but it results wrong output and i couldn't find correct query

Solution

  • You can generate this output by joining the table_system table to the table_drive table, twice:

    SELECT ts.name, td1.model AS drive_1, td2.model AS drive_2
FROM table_system ts
LEFT JOIN table_drive td1
    ON td1.id = ts.drive_1
LEFT JOIN table_drive td2
    ON td2.id = ts.drive_2;