Question 113042 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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?

Tags:

2

Answers


  1. 0

    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…

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search