How to select single data from a Join conditional?

Question

I have 2 database tables:

Servers

+----------+--------------+
| Field    | Type         |
+----------+--------------+
| id       | int(11)      |
| ip       | varchar(255) |
| votes    | int(11)      |
| port     | varchar(255) |
| hash     | varchar(255) |
| created  | datetime     |
| modified | datetime     |
+----------+--------------+

Stats

+------------+--------------+
| Field      | Type         |
+------------+--------------+
| id         | int(11)      |
| server_id  | int(11)      |
| type       | varchar(255) |
| hostname   | varchar(255) |
| gamemode   | varchar(255) |
| language   | varchar(255) |
| players    | int(11)      |
| slots      | int(11)      |
| version    | varchar(255) |
| url        | varchar(255) |
| date_check | datetime     |
| created    | datetime     |
| modified   | datetime     |
+------------+--------------+

I’m having trouble making the following rule of selection:

Select the table data Servers and make a Join with the Stats using the Servers.id = Stats.server_id. But this John must only take the most recent data (only one) table Stats that has the type equal to online or offline.

How could the query used to make this rule?

Answer 1

See if it helps?

select sv.*
from servers sv
       inner join stats st
         on st.server_id = sv.id
where st.type in ('inline','offline')
order by st.modified desc
limit 1

Answer 2

See how to do below, this solution was made in SQL SERVER, but select is equivalent to mysql.

declare  @servers table
(
     id        int,      
     ip        varchar(255) ,
     votes     int,      
     port      varchar(255), 
     hash      varchar(255), 
     created   datetime ,    
     modified  datetime    
)

declare @stats table
(
     id          int,      
     server_id   int,      
     type        varchar(255), 
     hostname    varchar(255), 
     gamemode    varchar(255), 
     language    varchar(255), 
     players     int,      
     slots       int,     
     version     varchar(255),
     url         varchar(255), 
     date_check  datetime,     
     created     datetime,     
     modified    datetime     
)

insert into @servers values
(1,'162.198.1.1',12,'80','3232323',getdate(), getdate()),
(2,'162.198.1.1',12,'8080','3232323',getdate(), getdate())

insert into @stats values
(1,1,'offline', 'teste/teste','','',1,1,'','',getdate(),CONVERT(varchar(10),(dateadd(dd, -4, getdate())),120), getdate()),
(2,1,'online ', 'teste/teste','','',1,1,'','',getdate(),CONVERT(varchar(10),(dateadd(dd, -3, getdate())),120), getdate()),
(3,1,'online ', 'teste/teste','','',1,1,'','',getdate(),CONVERT(varchar(10),(dateadd(dd, -2, getdate())),120), getdate()),
(4,1,'offline', 'teste/teste','','',1,1,'','',getdate(),CONVERT(varchar(10),(dateadd(dd, -1, getdate())),120), getdate()),
(5,1,'não select', 'teste/teste','','',1,1,'','',getdate(),getdate(), getdate()),
(11,1,'offline', 'teste/teste','','',1,1,'','',getdate(),getdate(), getdate()),

(6,2,'offline', 'teste/teste2','','',1,1,'','',getdate(),CONVERT(varchar(10),(dateadd(dd, -4, getdate())),120), getdate()),
(7,2,'offline', 'teste/teste2','','',1,1,'','',getdate(),CONVERT(varchar(10),(dateadd(dd, -3, getdate())),120), getdate()),
(8,2,'offline', 'teste/teste2','','',1,1,'','',getdate(),CONVERT(varchar(10),(dateadd(dd, -2, getdate())),120), getdate()),
(9,2,'offline', 'teste/teste2','','',1,1,'','',getdate(),CONVERT(varchar(10),(dateadd(dd, -1, getdate())),120), getdate()),
(10,2,'online', 'teste/teste2','','',1,1,'','',getdate(),getdate(), getdate()),
(12,2,'não select', 'teste/teste','','',1,1,'','',getdate(),getdate(), getdate())


select * from @servers sv
join @stats s
on s.server_id = sv.id
and s.created = (select max(created) from @stats s1 where s1.server_id = sv.id)
where s.type in ('online','offline')