How to select single data from a conditional join?

Question

How to select single data from a conditional join?

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

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 selection rule:

Select the data from the servers table and join stats using servers.id = stats.server_id . But this join should only get the latest (only one) of the stats table that has type equal to online in> or offline .

How could the query be used to make this rule?

mysql sql join select

asked by anonymous 13.11.2016 / 20:33

2 answers

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

14.11.2016 / 12:11

checks if the input value has changed with jQuery How to join 2 tables without repeating data?

score 1 · Accepted Answer

Here's how to do it, this solution was done 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')