CODEWITHSUNDEEP
mysqlreturnrow
posop
posop

Reputation: 531

MySQL return multple row values in one select

I am trying to prepare a report from my database.

I have 2 tables like so:

device          sensor
id  | name      id  | devid | value
====|=====      ====|=======|========
1   | dev1      1   |   1   | 1.1
2   | dev2      2   |   1   | 1.2
                3   |   2   | 2.1
                4   |   2   | 2.2

I want to get a return of this:

 id | name  | val_rc    | val_cf
====|=======|===========|==========
 1  | dev1  | 1.1       | 1.2
 2  | dev2  | 2.1       | 2.2

I am using a for statement to put all the values into my report. The header names are not important.

I have tried different permutations of this SQL query:

SELECT device.id, device.name, sensor.value AS val_rc, sensor.value AS val_cf
FROM device
JOIN sensor
ON sensor.devid = device.id;

Upvotes: 0

Views: 155

Answers (3)

posop
posop

Reputation: 531

After looking at what you wrote here is what I came up with:

SELECT device.id, device.name,
(SELECT sensor.value FROM sensor WHERE sensor.devid = device.id AND sensor.senid = '1') AS val_rc, 
(SELECT sensor.value FROM sensor WHERE sensor.devid = device.id AND sensor.senid = '2') AS val_cf  
FROM device

Upvotes: 0

Majid Laissi
Majid Laissi

Reputation: 19778

You need to join the table device twice with sensor.

Assuming you want the value for rc to be the first value in the table and cf the last one, this query :

SELECT device.id, device.name, sensor1.value AS val_rc, sensor2.value AS val_cf
FROM device, sensor sensor1, sensor sensor2
    where sensor1.devid= device.id 
    and sensor1.id = (select min(id) from sensor s1 where s1.devid= sensor1.devid)
    and sensor2.devid= device.id 
    and sensor2.id = (select max(id) from sensor s2 where s2.devid= sensor2.devid)

will give exactly what you asked.

 id | name  | val_rc    | val_cf
====|=======|===========|==========
 1  | dev1  | 1.1       | 1.2
 2  | dev2  | 2.1       | 2.2

Upvotes: 0

Taryn
Taryn

Reputation: 247630

You provided no details about how you want to decide which value goes in each column, so this example uses a row_number:

select id,
   name,
   max(case when GROUP_ROW_NUMBER = 1 then value end) as val_rc,
   max(case when GROUP_ROW_NUMBER = 2 then value end) as val_cf
from
(
  select d.id, devid, name, value, 
        @num := if(@NAME = `NAME`, @num + 1, 1) as group_row_number,
        @NAME := `NAME` as dummy
  FROM device d
  INNER JOIN sensor s
    ON s.devid = d.id
  order by name, value
) x
group by id, name

See SQL Fiddle with Demo

Upvotes: 1

Related Questions