CODEWITHSUNDEEP
mysql
Quang Anh Chu
Quang Anh Chu

Reputation: 57

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 ?

Upvotes: 4

Views: 102

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520988

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

Upvotes: 2

tjcertified
tjcertified

Reputation: 704

check out this SQL Fiddle for proof that this query works:

http://sqlfiddle.com/#!9/13845c/4/0

Basically, the query looks as follows:

SELECT r.Name, p1.positionCode As Direct, p2.positionCode as Supervise
FROM responsibilities r 
LEFT JOIN positions p1 ON r.Direct = p1.positionID
LEFT JOIN positions p2 ON r.Supervise = p2.positionID

Upvotes: 0

Piyush Gupta
Piyush Gupta

Reputation: 2179

Try this Query,

SELECT r.Name,
       p1.positionCode AS Direct,
       p2.positionCode AS Supervise
FROM responsibilities r
LEFT JOIN positions p1
    ON r.Direct = p1.positionID
LEFT JOIN positions p2
    ON r.Supervise = p2.positionID

Output: SEE SQLFiddle DEMO

Upvotes: 3

smali
smali

Reputation: 4805

Try following Query, It should work

select R.Name,(select P.positionCode where R.Direct=P.positionID) as 
Direct,(select P.positionCode where R.Supervise=P.positionID) as 
Supervise from Responsibilites R, Positions P;

Upvotes: 0

Related Questions