CODEWITHSUNDEEP
mysqlsql
Richard Octovianus
Richard Octovianus

Reputation: 180

mysql join 2 tables, 2 columns

let's say I have

table student_data and fields are


╔════╦════════════╦════════════╗
║ ID ║ CREATED_BY ║ UPDATED_BY ║
╠════╬════════════╬════════════╣
║  1 ║          1 ║          2 ║
╚════╩════════════╩════════════╝

table creator_updater and fields are


╔════╦════════════════╗
║ ID ║      NAME      ║
╠════╬════════════════╣
║  1 ║ The Creator    ║
║  2 ║ Second Creator ║
╚════╩════════════════╝

so I wonder what is the exact code so it will show

student_data

╔════╦═══════════════╦══════════════════════╗
║ ID ║ CREATED_BY_ID ║ LATEST_UPDATED_BY_ID ║
╠════╬═══════════════╬══════════════════════╣
║  1 ║ The Creator   ║ Second Creator       ║
╚════╩═══════════════╩══════════════════════╝

i tried join syntax, but it seems different

Upvotes: 2

Views: 1470

Answers (1)

John Woo
John Woo

Reputation: 263893

you need to join the creator_updater table twice so you can get the names of the two columns.

SELECT  a.id,
        b.name created_by_id,
        c.name latest_updated_by_id
FROM    student_data a
        INNER JOIN creator_updater b
            ON a.created_by = b.id
        INNER JOIN creator_updater c
            ON a.updated_by = c.id

but the code above will not work if one of the columns is nullable, if that's the case, use LEFT JOIN instead of INNER JOIN

SELECT  a.id,
        b.name created_by_id,
        c.name latest_updated_by_id
FROM    student_data a
        LEFT JOIN creator_updater b
            ON a.created_by = b.id
        LEFT JOIN creator_updater c
            ON a.updated_by = c.id

For more information about joins

Upvotes: 4

Related Questions