Mysql – How to join two result sets from same table with different conditions on same column

MySQL

select * from unit_of_measure;
+--------+-----------+--------+------------+--------------+-----------------+-------------+---------------+------------------+
| uom_id | uom_name  | symbol | created_by | created_date | created_ip_addr | modified_by | modified_date | modified_ip_addr |
+--------+-----------+--------+------------+--------------+-----------------+-------------+---------------+------------------+
|      1 | number    | NULL   | NULL       | NULL         | NULL            | NULL        | NULL          | NULL             |
|      2 | kilograms | NULL   | NULL       | NULL         | NULL            | NULL        | NULL          | NULL             |
|      3 | liters    | NULL   | NULL       | NULL         | NULL            | NULL        | NULL          | NULL             |
+--------+-----------+--------+------------+--------------+-----------------+-------------+---------------+------------------+

From this table I am trying to combine two result sets like:

select uom_name as uomname1 from unit_of_measure where uom_id=1;

select uom_name as uomname2 from unit_of_measure where uom_id=2;

Can anyone help me to join these two result sets. I have tried with union but it didn't solve the issue.

Best Answer

If you want number and kilograms to appear as a single column (comma separated), do this:

SELECT GROUP_CONCAT(uom_name) UnitsOfMeasure
FROM unit_of_measure WHERE uom_id IN (1,2);

If you want number and kilograms to appear as a single column (space separated), do this:

SELECT GROUP_CONCAT(uom_name SEPARATOR ' ') UnitsOfMeasure
FROM unit_of_measure WHERE uom_id IN (1,2);

If you want number and kilograms to appear as separate columns, do this:

SELECT * FROM
(SELECT uom_name AS uomname1 FROM unit_of_measure WHERE uom_id=1) A,
(SELECT uom_name AS uomname2 FROM unit_of_measure WHERE uom_id=2) B;

Give it a Try !!!

Related Question