Quang Anh Chu Quang Anh Chu - 4 months ago 10
MySQL Question

Alter columns from selected result table in MYSQL

Here the things,I want to join table '

responsibilities
' with fields
Name, Direct, Supervise
:

Name | Direct | Supervise
ABC 2 4


and table 'positions' with positionCode, positionID:

positionCode | positionID
HR/HRM 2
HR/MN 4


The selected result table will be some thing like this.

Name | Direct | Supervise
ABC HR/HRM HR/MN


The '
Direct
' and '
Supervise
' column should be
positionCode
from 'positions' table. Is there an all-in-one query to output this result? Or I have to query 2 times ?

Answer

I think you can join responsibilities twice to the positions table:

SELECT r.Name,
       COALESCE(p1.positionCode, 'Direct is N/A') AS Direct,
       COALESCE(p2.positionCode, 'Supervise is N/A') AS Supervise
FROM responsibilities r
LEFT JOIN positions p1
    ON r.Direct = p1.positionID
LEFT JOIN positions p2
    ON r.Supervise = p2.positionID

Follow the link below for a running demo:

SQLFiddle

Comments