Query MySQL - One table view multiples records

Question

I have a table with this contents:

Table1: MySQL Table Content ( Table: 'sensors' )

        Date         |    Topic    |  Value
-------------------------------------------
2016/06/15 13:22:11  |  pressure   |  10.52
2016/06/15 13:22:11  | temperature |  55.25
2016/06/15 13:23:42  | temperature |  40.12
2016/06/15 13:25:10  |  pressure   |   8.10
2016/06/15 13:25:13  | temperature |  47.24
2016/06/15 13:27:30  |  pressure   |  11.47
2016/06/15 13:27:30  | temperature |  52.27

And I want to do a select to retrive this view:

Table2: Query result from table 'sensors'

        Date         |  pressure   |  temperature
-------------------------------------------------
2016/06/15 13:22:11  |    10.52    |    55.25
2016/06/15 13:23:42  |             |    40.12
2016/06/15 13:25:10  |     8.10    |
2016/06/15 13:25:13  |             |    47.24
2016/06/15 13:27:30  |    11.57    |    52.27

I want select DISTINCT Date from 'sensors' table and then fill with the value from topic (if not exist at that time the value will be empty)

PS. Table 'sensors' have multiples topics, but I only need the 'pressure' and 'temperature' values to build a chart.

Thanks

But I dont know how to do a Cross Join with Distinct..!! Do you have any sample of code?? — Pmoshbr, Jun 16 '16 at 22:09

score 0 · Accepted Answer · answered Jun 16 '16 at 23:58

I think should work

SELECT t1.Date as ReadingDate, Max(pressure) as Pressure, Max(temperature) as Temperature FROM 
(
  SELECT * FROM 
  (SELECT sensor.Date, "" AS pressure, sensor.value AS temperature
  FROM sensor
  WHERE sensor.topic="temperature") tbl_temp
UNION ALL
  SELECT * FROM 
  (SELECT sensor.Date, sensor.value AS pressure, "" AS temperature
  FROM sensor
  WHERE sensor.topic="pressure") tbl_pressure
) AS t1
GROUP BY t1.Date

score -2 · Answer 2 · edited May 23 '17 at 12:31

-2

All the explenation you need about cross joins & the difference with innerjoins can you find here hope this helps you out. You need to make a cross join

CROSS JOIN vs INNER JOIN in SQL Server 2008

You should try. I know this is a inner join but it should work fine.

SELECT DISTINCT s1.Date, s1.Value, s2.Value
FROM Sensor s1
    INNER JOIN Sensor s2
    ON s1.Date= s2.Date
ORDER BY s1.Date

edited May 23 '17 at 12:31

Community

1
1

answered Jun 16 '16 at 22:02

Dylan Gomes

93
11

Pointing out the documentation is not a whole more helpful than just the comment I left. If you are going to leave an answer, give the SQL Query demonstrating how to do it. – Guillaume F. Jun 16 '16 at 22:04
You're not displaying the topic associated with the value – dbmitch Jun 16 '16 at 22:12
Sorry, but I don't understand the 'pv1' and 'pv2', could please be more specific? – Pmoshbr Jun 16 '16 at 22:24
the pv1 & pv2 where mistakes i replaced them now with s1 & s2 Its the name of the table but they are both the same – Dylan Gomes Jun 16 '16 at 22:51

Query MySQL - One table view multiples records

2 Answers2