Display mysql results in one row

mysql> select * from facts;
+----+---------+------------+---------+
| id | fact_id | fact_value | host_id |
+----+---------+------------+---------+
|  1 |       1 | rh5        |       1 |
|  2 |       1 | rh4        |       2 |
|  3 |       2 | virtual    |       1 |
|  4 |       2 | virtual    |       2 |
|  5 |       3 | rack 2     |       1 |
+----+---------+------------+---------+

mysql> select * from hosts;
+---------+-----------+
| host_id | host_name |
+---------+-----------+
|       1 | bellagio  |
|       2 | mirage    |
+---------+-----------+

The query I used does the following:

mysql> select host_name,fact_value from hosts as a left join facts as b on 
     >   b.host_id=a.host_id and b.fact_id in (1,2,3);

+-----------+------------+
| host_name | fact_value |
+-----------+------------+
| bellagio  | rh5        |
| bellagio  | virtual    |
| bellagio  |开发者_JS百科 rack 2     |
| mirage    | rh4        |
| mirage    | virtual    |
+-----------+------------+

I want the results to print one row for each host, notice how it prints each fact_value on a separate row. The reason why i have the IN clause is that this table has over 40 possible columns for each host. I only want a handful (I select 3 in this example).

Here's what i'm looking for.

+-----------+--------+-----------+-----------+
| host_name | value1 |  value 2  |  value 3  |
+-----------+--------+-----------+-----------+
| bellagio  | rh5    |  virtual  |  rack 2   |
| mirage    | rh4    |  virtual  |  NULL     |
+-----------+--------+-----------+-----------+

SELECT GROUP_CONCAT(fact_value)...
...
GROUP BY host_name

You could try creating a view on each of the value columns and then joining the columns.

create view [first_values] as 
  select fact_value, host_id 
  from facts where fact_id = 1;

create view [second_values] as 
  select fact_value, host_id 
  from facts where fact_id = 2;

create view [third_values] as 
  select fact_value, host_id 
  from facts where fact_id = 3;

now join the columns:

select h.host_name, f.fact_value as value1, s.fact_value as value2, t.fact_value as value3 

from hosts as h 
  left join [first_values] as f on h.host_id = f.host_id 
  left join [second_values] as s on h.host_id = s.host_id 
  left join [third_values] as t on h.host_id = t.host_id;

This works ... granted some of the field names have changed since I last posted.

select distinct t0.host_id, t1.name, t2.value as os_type, t3.value as ip_addr, t4.value as rack, t5.value as host_owner, t6.value as host_memory,
         t7.value as host_swap, t8.value as host_make, t9.value as host_model
             FROM fact_values t0 left outer join hosts t1 on (t0.host_id=t1.id)
                  left outer join fact_values t2 on (t0.host_id=t2.host_id and t2.fact_name_id = 30)
                  left outer join fact_values t3 on (t0.host_id = t3.host_id and t3.fact_name_id = 13)
                  left outer join fact_values t4 on (t0.host_id = t4.host_id and t4.fact_name_id = 65)
                  left outer join fact_values t5 on (t0.host_id = t5.host_id and t5.fact_name_id = 81)
                  left outer join fact_values t6 on (t0.host_id = t6.host_id and t6.fact_name_id = 18)
                  left outer join fact_values t7 on (t0.host_id = t7.host_id and t7.fact_name_id = 51)
                  left outer join fact_values t8 on (t0.host_id = t8.host_id and t8.fact_name_id = 36)
                  left outer join fact_values t9 on (t0.host_id = t9.host_id and t9.fact_name_id = 47)
             WHERE t1.name = '$hostname'

SELECT u.user_id,u2.acc_no AS pacc,u3.acc_no AS sacc
FROM table_name AS u  
LEFT JOIN table_name AS u2 ON u2.user_id=u.user_id AND u2.acc_type='P' AND u2.lab_id = ? AND u2.category = 'SOMETHING'
LEFT JOIN table_name AS u3 ON u3.user_id=u.user_id AND u3.acc_type='S' AND u3.lab_id = ? AND u3.category = 'SOMETHING'
GROUP BY u.user_id

I have used the abouve query to get multiple rows to single row from the same table . Which is worked fine with me ..