[RESOLVED] Need help creating an SQL query.

Here's the data I have:

PHP Code:

---

TABLE1

TABLE1ID    roomID    objectID    timeStamp
1            3            5        1254807439
2            3            6        1254807439
3            3            7        1254807439
4            3            8        1254807439
5            3            5        1254287435
6            3            6        1254287435
7            3            7        1254287435
8            3            8        1254287435
9            5            5        1254807439
10            5            6        1254807439
11            5            7        1254807439
12            5            8        1254807439
13            5            5        1254287435
14            5            6        1254287435
15            5            7        1254287435
16            5            8        1254287435


ROOMTABLE

roomID    roomLocation    roomBuilding    roomDescription
3        2.55                m06        First Room
5        1.23                m06        Second Room
6        1.25                m06        Third Room 


---

What I need to do is return the list of rooms and room location, building etc in a table, but ordered either asc or desc by the timestamp. This part I can do. I also need to only return a distinct set of rooms, ie, I can't have duplicated rooms.

So basically, the result from the table above should be:
RESULTS:

PHP Code:

---

roomID        roomLocation    roomBuilding    roomDescription        timestamp
3                2.55                m06            First Room        1254807439
5                1.23                m06            Second Room        1254807439
6                1.25                m06            Third Room            0 


---

When ever I attempt this, using INNER JOIN or LEFT JOIN and connecting up the tables I always get duplicated rooms (Because of the different dates). If I use distinct, I will still get duplicated rooms.

I basically need unique room results, with the latest date.

Could anyone offer any help to achieve this query?

What is the point of "object ID" Is that one of the fields you want in the table?

Edit:

How many tables do you need?

There is no point of object id in this instance. It's used in another area of the script though. The script is basically used to check when the rooms were last maintenanced (Some rooms have computers, or DVDs, projectors etc, another table has the object name linked with the object id). I just put them there to keep it similar to the current table schema.

So no, it's not one of the fields I want in the results table.

I'm not sure what you mean about how many tables I need? I just need a query that will get the data from the 2 example tables, and give a table like the results table.

Thanks :D.

Does this help?

(not tested code)

sql Code:

SELECT ROOMTABLE.roomID, roomLocation, roomBuilding, roomDescription, MAX(timestamp)
FROM ROOMTABLE
LEFT JOIN TABLE1 ON ROOMTABLE.roomID = ROOMTABLE.roomID 
GROUP BY ROOMTABLE.roomID, roomLocation, roomBuilding, roomDescription

Here is the code to create the example database and table:

PHP Code:

---

$sql = "CREATE TABLE `Building`.`Room Details` (`RoomID` TEXT NOT NULL, `ObjectID` TEXT NOT NULL, `Timestamp` TIMESTAMP NOT NULL, `Room Location` TEXT NOT NULL, `Room Building` TEXT NOT NULL, `Room Description` TEXT NOT NULL) ENGINE = MyISAM;";

INSERT INTO `Building`.`room details` (`RoomID`, `ObjectID`, `Timestamp`, `Room Location`, `Room Building`, `Room Description`) VALUES ('1', '1', CURRENT_TIMESTAMP, '225', '03', 'Big'); 


---

Here is code to create the database:

PHP Code:

---

// Database connection variables
$dbDatabase = "Building";

//connect to db
$conn = @mysql_connect("localhost", "user", "");
if (!$conn) {
die("Connection failed: " .mysql_error());
}

//create database
$query = "CREATE DATABASE IF NOT EXISTS Building";
if (mysql_query($query, $conn)) {
echo ("Database create query successful!");
}else {
die ("Database query failed: " .mysql_error());
}
//select database
if (mysql_select_db($dbDatabase, $conn)) {
echo ("Database selection successful!");
}else {
die ("Could not locate test database" .mysql_error());
} 


---

I believe this would work, if I could somehow use the MAX function in the WHERE clause. At the moment the MAX function only prints out one room - the room with the closest date.

I'm still working on it though, I think I'm just going to have to resort to code and arrays to do this though.

Quote:

---

Originally Posted by Slyke

I believe this would work, if I could somehow use the MAX function in the WHERE clause. At the moment the MAX function only prints out one room - the room with the closest date.

I'm still working on it though, I think I'm just going to have to resort to code and arrays to do this though.

---

What is the code you have at the moment??

It's a bit long, but I've solved it.

I put the room IDs that have been printed to the screen into an array and used the in_array function each time I was going to print to the screen. That way I can be sure that there'll be no duplicates.

It would have been better to do this in SQL, but this is just as good :).

The SQL was just:

PHP Code:

---

SELECT DISTINCT `TABLE1`.`TABLE1ID`, `TABLE1`.`timeStamp` FROM `TABLE1`; 


---

this query does what you want. just change the table names.

SELECT r.roomid, r.roomlocation, r.roombuilding, r.roomdescription, max(rs.timestamp) AS roomtimestamp FROM `test_rooms` r LEFT JOIN `test_roomservice` rs ON r.roomid=rs.roomid GROUP BY r.roomid ORDER BY roomtimestamp DESC