MySQL trying to select the same column twice, but it only returns the value once (no join)-CodePudding

What I am trying to do is to get the same column result twice with a single query.

SELECT appellation FROM persona WHERE character_id IN (853,12,853) ORDER BY FIELD(character_id,853,12,853)

This returns the result:

character_id | appellation
---------------------------
853          | John Cena
12           | Chris Brown

But what I am looking for to return is:

character_id | appellation
---------------------------
853          | John Cena
12           | Chris Brown
853          | John Cena

Is there a way in MySQL to get that result?

CodePudding user response：

To do this, you can provide a values table and join persona from it:

select character_id,appallation
from (select null cid where 0 union all values row(853),row(12),row(853)) cids
join persona on character_id=cid

(left join if you want rows even if there's no matching persona)

The select...where 0 is just to give the column a meaningful name.

fiddle

Note that you will not necessarily get them in the order specified in the list; to do that you need to provide something to order on:

select character_id,appallation
from (select null cid, null roworder where 0 union all values row(853,1),row(12,2),row(853,3)) cids
join persona on character_id=cid
order by roworder

CodePudding user response：

You can achieve this result using UNION:

SELECT * FROM (    
    SELECT character_id, appellation, CONCAT(character_id, '-1') AS identifier FROM persona WHERE character_id IN (853,12)
    UNION 
    SELECT character_id, appellation, CONCAT(character_id, '-2') AS identifier FROM persona WHERE character_id IN (853) 
) AS tmp ORDER BY FIELD(tmp.character_id, 853,12,853)

It seems strange that you want the same row twice though...