CODEWITHSUNDEEP
phpmysql
Rick van Ittersum
Rick van Ittersum

Reputation: 63

Get newest record out of mysql database multiple tables

I have three tables. one with user details:

---------------------------------
| Username | Phonenumber        |
---------------------------------
| user1    | 0000000000         |
---------------------------------
| user2    | 000000000          |
---------------------------------

Table two containing contact info, this is where users are linked together when they "know" each other:

------------------------
| ID | User  | Friend  |
------------------------
| 1  | 1     | 2       |
------------------------
| 2  | 1     | 3       |
------------------------
| 3  | 3     | 2       |
------------------------

And the last one, a table which contains location info:

----------------------------------------------------------
| ID | UserID  | TimeStamp         | Lng      | Lat      |
----------------------------------------------------------
| 1  | 1     | 2015-01-07 19:54:23 | lngvalue | latvalue |
----------------------------------------------------------
| 2  | 1     | 2015-01-07 19:54:26 | lngvalue | latvalue |
----------------------------------------------------------
| 3  | 2     | 2015-01-07 19:53:52 | lngvalue | latvalue |
----------------------------------------------------------

Now I want to select the Username and Phone number from the friends of user 1, and also the latest location of the contact users.

I've created a query, but it shows me the oldest lng/lat values of the user, while I need the newest. 

SELECT user.Username, user.PhoneNumber, location.Lng, location.Lat
FROM contact, user, location
WHERE User.ID IN (SELECT contact.Friend FROM contact WHERE contact.User = 1) 
AND user.ID = location.UserID
GROUP BY user.Username

Upvotes: 1

Views: 44

Answers (2)

Rick van Ittersum
Rick van Ittersum

Reputation: 63

I've made a minor change in the query from Randy, becouse I can also select the newest record by checking the highest ID from the location table. The query as it works for me: 

SELECT user.Username, user.PhoneNumber, location.Lng, location.Lat
FROM contact, user, 
( select max(id) maxid, userid from location group by userid ) loc
, location
WHERE User.ID IN (SELECT contact.Friend FROM contact WHERE contact.User = 1) 
AND user.ID = loc.UserID
and location.id = loc.maxid
GROUP BY user.Username

Upvotes: 1

Randy
Randy

Reputation: 16677

basic idea: create an inline view of the lat and log records you want.. then join to that.

SELECT user.Username, user.PhoneNumber, location.Lng, location.Lat
FROM contact, user, 
( select id, userid, max(timestamp) ts from location group by userid ) loc
, location
WHERE User.ID IN (SELECT contact.Friend FROM contact WHERE contact.User = 1) 
AND user.ID = loc.UserID
and location.id = loc.id
and location.timestamp = loc.ts
GROUP BY user.Username

Upvotes: 0

Related Questions