在没有唯一键的桌面上查询MySQL

I'm trying to query a database that I have in MySQL. I need to union this with another query from a traditional table, so I'd like it all done in php. Here is how I'm currently doing this, which I think may be very inefficient.

This is a sample of the database: http://sqlfiddle.com/#!2/874e19/3

This is how I'm selecting the information:

$device = $mysqli->query("SELECT 
DISTINCT
serial
FROM
device
ORDER BY 
edit_date 
DESC
")or die(mysql_error());

while ($row = $device->fetch_assoc()) {

$serial = $row['serial'];

$device_data = $mysqli->query('SELECT
(SELECT data_value FROM device WHERE (data_type = \'device_name\' AND serial = \''. $serial . '\')) as hostname,
(SELECT data_value FROM device WHERE (data_type = \'unit\' AND serial = \''. $serial . '\')) as unit,
(SELECT data_value FROM device WHERE (data_type = \'version\' AND serial = \''. $serial . '\')) as version,
(SELECT data_value FROM device WHERE (data_type = \'lan_ip\' AND serial = \'' . $serial .'\')) as location_ip,
(SELECT data_value FROM device WHERE (data_type = \'wan_ip\' AND serial = \'' . $serial .'\')) as wan_ip,
(SELECT data_value FROM device WHERE (data_type = \'status\' AND serial = \'' . $serial .'\')) as status
')or die(mysql_error());
}

Is there a better way to do this?

I would do that in just one query for best performance and simplicity. Something like this two solutions should work:

SELECT DISTINCT d.serial, d.data_value AS hostname, d1.data_value AS unit, d2.data_value AS version, d3.data_value AS lan_ip, d4.data_value AS wan_ip, d5.data_value AS status
  FROM device d
  LEFT JOIN device d1 ON d.serial = d1.serial AND d1.data_type = 'unit'
  LEFT JOIN device d2 ON d.serial = d2.serial AND d2.data_type = 'version'
  LEFT JOIN device d3 ON d.serial = d3.serial AND d3.data_type = 'lan_ip'
  LEFT JOIN device d4 ON d.serial = d4.serial AND d4.data_type = 'wan_ip'
  LEFT JOIN device d5 ON d.serial = d5.serial AND d5.data_type = 'status'
WHERE d.data_type = 'device_name'
ORDER BY d.edit_date DESC;

or

SELECT DISTINCT d.serial, d.data_value AS hostname,
    (SELECT data_value FROM device WHERE (data_type = 'unit' AND serial = d.serial)) AS unit,
    (SELECT data_value FROM device WHERE (data_type = 'version' AND serial = d.serial)) AS version,
    (SELECT data_value FROM device WHERE (data_type = 'lan_ip' AND serial = d.serial)) AS location_ip,
    (SELECT data_value FROM device WHERE (data_type = 'wan_ip' AND serial = d.serial)) AS wan_ip,
    (SELECT data_value FROM device WHERE (data_type = 'status' AND serial = d.serial)) AS status)
  FROM device d 
WHERE d.data_type = 'device_name'
ORDER BY d.edit_date DESC;