5
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.

2

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 !!!

0

Try his code :

  select u1.uom_name as uomname1 
    from unit_of_measure  as u1 inner join unit_of_measure  as u2
     on u1.uom_id=u2.uom_id
     where u1.uom_id=1 
    ans u2.uom_id;

But you can get the same result with :

select uom_name from unit_of_measure where uom_id in (1,2);
0

RolandoMySQLDBA already covered most of it. Here's an alternative to his third suggestion:

select max(uomname1) as uomname1
     , max(uomname2) as uomname2
from (
    select case when uom_id=1 then uom_name end as uomname1
         , case when uom_id=2 then uom_name end as uomname2
    from unit_of_measure
    where uom_id IN (1,2)
) as x;
0

You just have to use UNION clause between them:

SELECT uom_name AS uomname1 FROM unit_of_measure WHERE uom_id=1
 UNION
SELECT uom_name AS uomname2 FROM unit_of_measure WHERE uom_id=2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.