¿Cómo puedo get un valor de campo en 3 tablas de asociación con el máximo de date

Entonces, tengo una tabla y quiero get el valor de un campo en el logging con el mayor valor de DateTime() en otro campo y donde aún otro campo es igual a un cierto valor.

  -- -- Structure de la table `site` -- CREATE TABLE IF NOT EXISTS `site` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `site_name` varchar(15) NOT NULL, `city` varchar(50) DEFAULT NULL, `address` varchar(80) DEFAULT NULL, `entity` varchar(50) DEFAULT NULL, `status_activity` tinyint(1) NOT NULL DEFAULT '1', `infra` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `idx_site_name` (`site_name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ; -- -- Contenu de la table `site` -- INSERT INTO `site` (`id`, `site_name`, `city`, `address`, `entity`, `status_activity`, `infra`) VALUES (1, 'FR001', 'tttt', 'tttttttttt', 'yyyyy', 1, 0), (2, 'FR002', 'ccccc', 'cccccccccc', 'rrrrrrrrrrrr', 1, 0); -- -------------------------------------------------------- -- -- Structure de la table `site_topology` -- CREATE TABLE IF NOT EXISTS `site_topology` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `id_site` int(10) unsigned NOT NULL, `id_topology` int(10) unsigned NOT NULL, `date` date NOT NULL, PRIMARY KEY (`id`), KEY `idx_site_date` (`date`), KEY `fk_id_site_2` (`id_site`), KEY `fk_id_topology_2` (`id_topology`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; -- -- Contenu de la table `site_topology` -- INSERT INTO `site_topology` (`id`, `id_site`, `id_topology`, `date`) VALUES (1, 1, 1, '2015-03-03'), (2, 2, 3, '2015-03-03'), (3, 1, 2, '2015-04-30'), (4, 2, 5, '2015-04-30'), (5, 1, 1, '2015-06-25'), (6, 2, 4, '2015-06-25'); -- -------------------------------------------------------- -- -- Structure de la table `topology` -- CREATE TABLE IF NOT EXISTS `topology` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) NOT NULL, PRIMARY KEY (`id`), KEY `idx_type_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ; -- -- Contenu de la table `topology` -- INSERT INTO `topology` (`id`, `name`) VALUES (3, 'C1/C2'), (2, 'C3'), (5, 'HBN'), (4, 'Infrastruc'), (6, 'LB'), (1, 'SHBN'); -- -- Contraintes pour les tables exportées -- -- -- Contraintes pour la table `site_topology` -- ALTER TABLE `site_topology` ADD CONSTRAINT `fk_id_site_2` FOREIGN KEY (`id_site`) REFERENCES `site` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `fk_id_topology_2` FOREIGN KEY (`id_topology`) REFERENCES `topology` (`id`) ON DELETE CASCADE ON UPDATE CASCADE 

quiero ver

 FR001 SHBN 2015-06-26 FR002 Infrastruct 2015-06-26 

pero cuando ejecuto mi consulta tengo esto

 FR001 SHBN 2015-06-26`enter code here` FR002 C1/C2 2015-06-26 

¿Alguien puede ayudarme?

Tuvo un problema sobre cómo elegir la date máxima, ya que el mes se mostraba primero. Una forma diferente es mostrar el valor max_date (de una subconsulta ). Después de eso, puede comparar por las diferentes partes de la date . Código de la siguiente manera:

 select site_name, site_date, name from ( select site_name, st.date as site_date, name, s.id, (select max(sto.date) from site_topology sto) as max_date from site s inner join site_topology st on s.id =st.id_site inner join topology t on st.id_topology = t.id ) as v where YEAR(site_date) = YEAR(max_date) AND MONTH(site_date) = MONTH(max_date) AND DAY(site_date) = DAY(max_date) group by name order by site_name 

Probando aquí .

use una subconsulta son los resultados totales correctos aquí

 select site_name,max(date), name from (select site_name, city, address, entity , status_activity, infra, st.*,name from site s inner join site_topology st on s.id =st.id_site inner join topology t on st.id_topology = t.id ) as v group by site_name