Mysql query from multiple tables

Post by **Jfn** » Sun Apr 12, 2009 10:45 am

I have several temperature sensors mounted in my house and I am trying to list the last known measurement for every sensor on a webpage.

Getting the right data from the database is a tough one, so maybe someone is able to help me out.

I have three tables:

Code: Select all

Devices 	DeviceDataOregonTempHum
- Id --------->	- DevId
- DevType   |	- DevTemp
	    |	- DevHum
	    |	- DevUpd
	    |
	    |	DeviceLocation
	    |->	- Id
		- Description

There is a one-to-many relation between the tables Devices and DeviceDataOregonTempHum and a one-on-one relation between Devices and DeviceLocation. For every sensor in table Devices I need the last entry from table DeviceDataOregonTempHum and the corresponding location from table DeviceLocation.

The following query nearly does what it needs to do. However: For every device in table Devices it displays all records from table DeviceData instead of only the last entry:

Code: Select all

SELECT t1.Id, t2.DevTemp, t2.DevHum, t2.DevUpd, t3.DevLoc
FROM Devices AS t1
JOIN DeviceDataOregonTempHum AS t2
ON (t2.DevId = t1.Id)
JOIN DeviceLocation AS t3
ON (t3.Id = t1.DevLoc)
WHERE (t1.DevType = '1');

The output looks something like this:

Code: Select all

| 39 |    19.4 |     55 | 2009-04-12 08:00:06 | Werkkamer       |
| 39 |    19.4 |     55 | 2009-04-12 09:00:06 | Werkkamer       |
| 39 |    19.9 |     57 | 2009-04-12 10:00:05 | Werkkamer       |
+----+---------+--------+---------------------+-----------------+
1329 rows in set (0.00 sec)

Adding a 'GROUP BY t1.Id' to the query nearly gives me the output I need, only it displays every FIRST record from table DeviceDataOregonTempHum instead of the last one and I have not been able yet to make it display the LAST record. I tried various GROUP BY and ORDER BY versions of the query, changed the order of the tables in the query, but still no luck.

Is there anyone out there who knows the solution?

Post by **Droezel** » Sun Apr 12, 2009 11:16 am

You need to play a bit with LEFT JOIN and RIGHT JOIN, INNER JOIN and OUTER JOIN. And make sure to add GROUP BY.

Post by **Droezel** » Sun Apr 12, 2009 11:20 am

Oh yeah, to select the last record you will have to add a subquery to find the last record.

Post by **Jfn** » Sun Apr 12, 2009 11:52 am

@Droezel: Your remark about a subquery to find the last record for every device made me try the following:

Table DeviceDataOregonTempHum contains a record for every sensor and is updated on an hourly basis. What if I select all records that are younger than 1 hour and use that to further narrow my query?

I changed the query to this:

Code: Select all

SELECT t2.Id, t1.DevTemp, t1.DevHum, t3.DevLoc, t1.DevUpd
FROM DeviceDataOregonTempHum AS t1
JOIN Devices AS t2 ON (t2.Id = t1.DevId)
JOIN DeviceLocation AS t3 ON (t3.Id = t2.DevLoc)
WHERE (HOUR (now() - TIMESTAMP(t1.DevUpd)) < 1)
ORDER BY t2.Id;

This gives me the output I need!

Code: Select all

+----+---------+--------+-----------------+---------------------+
| Id | DevTemp | DevHum | DevLoc          | DevUpd              |
+----+---------+--------+-----------------+---------------------+
|  5 |    15.3 |     70 | Garage          | 2009-04-12 11:00:06 |
|  6 |    19.4 |     58 | Toilet          | 2009-04-12 11:00:04 |
|  7 |    19.7 |     61 | Gang            | 2009-04-12 11:00:05 |
|  8 |    20.0 |     59 | Badkamer        | 2009-04-12 11:00:05 |
|  9 |    19.8 |     62 | Overloop        | 2009-04-12 11:00:04 |
| 10 |    19.4 |     57 | Slaapkamer      | 2009-04-12 11:00:04 |
| 11 |    20.2 |     51 | Bioscoop        | 2009-04-12 11:00:05 |
| 12 |    19.3 |     68 | Overloop Zolder | 2009-04-12 11:00:06 |
| 13 |    19.1 |     55 | Zolderkamer     | 2009-04-12 11:00:05 |
| 39 |    20.2 |     56 | Werkkamer       | 2009-04-12 11:00:05 |
+----+---------+--------+-----------------+---------------------+
10 rows in set (0.05 sec)

Thanks for the info!