mysql: merge two columns into two rows

I have the following statement

SELECT disease.id, disease.name, disease_synonym.name FROM disease JOIN disease_synonym where diseaseId=code

the result is a table with an id and two columns with the names. how can i transform this into 2 columns with only an id and the name? (of course, the id will now occur sever开发者_Go百科al times)

Bye,

Nico

Two ways come to mind...

Run the query twice (once for name, and once for synonym), then union the results together...

SELECT disease.id, disease.name FROM disease

UNION ALL

SELECT disease.id, disease_synonym.name FROM disease JOIN disease_synonym where diseaseId=code

Or join on a two row table, and use a CASE statement to do a pivot...

SELECT
  disease.id,
  CASE WHEN pivot.field = 'name' THEN disease.name
       WHEN pivot.field = 'syno' THEN disease_synonym.name
  END
FROM
  disease
INNER JOIN
  disease_synonym
    ON diseaseId=code
CROSS JOIN
  (SELECT 'name' AS field UNION ALL SELECT 'syno' AS field) AS Pivot

I think you want SELECT disease.id, disease_synonym.name FROM ... don't you? Just displaying the synonym names?