Xrhrft

This question has been asked at here as well.

i had 2 table, tableA and tableB looking like this:

tableA:

tableB:

i manage to get the latest record by auto increment id by using the following sql for tableA and tableB:

SELECT *

FROM tableA 

WHERE id IN (SELECT MAX(id) AS id

             FROM tableA 

             GROUP BY social_num) 





SELECT *

FROM tableB 

WHERE id IN (SELECT MAX(id) AS id

             FROM tableB 

             GROUP BY social_num) 

how to join them together, so that, i get the latest record on tableA and latest record on tableB link by social_num?
i tried following sql, it work:

SELECT *

FROM tableA 

LEFT JOIN tableB ON tableA.social_num = tableB.social_num

WHERE tableA.id IN (SELECT MAX(id) AS id

             FROM tableA 

             GROUP BY social_num) 

AND

tableB.id IN (SELECT MAX(id) AS id

             FROM tableB 

             GROUP BY social_num) 

but how do i show all of other user from tableA togather as well, like Morris Q

DDL Information

CREATE TABLE `tableA` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `fullname` varchar(255) DEFAULT NULL,

  `social_num` varchar(255) DEFAULT NULL,

  `email` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;



/*Data for the table `tableA` */



insert  into `tableA`(`id`,`fullname`,`social_num`,`email`) values (1,'David J','1155','davidj@gmail.com'),(2,'Brian H','2244','brianh@gmail.com'),(3,'Hawkins M','6677','hawkins@gmail.com'),(4,'Marry K','7122','marry@gmail.com'),(5,'Utah O','9123','utah@gmail.com'),(6,'James L','1266','james@gmail.com'),(7,'David J','1155','david@hotmail.com'),(8,'Marry K','7122','marry@gmail.com'),(9,'Johnson E','1180','johnson@gmail.com'),(10,'Hawkins M','6677','hawkins@yahoo.com'),(11,'Morris Q','1461','morris@gmail.com'),(12,'David J','1155','david@yahoo.com');



CREATE TABLE `tableB` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `fullname` varchar(255) DEFAULT NULL,

  `social_num` varchar(255) DEFAULT NULL,

  `phone_num` varchar(255) DEFAULT NULL,

  `fav_color` varchar(255) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;



/*Data for the table `tableB` */



insert  into `tableB`(`id`,`fullname`,`social_num`,`phone_num`,`fav_color`) values (1,'Brian H','2244','912-112-1231','blue'),(2,'Johnson E','1180','912-221-5512','red'),(3,'David J','1155','812-231-6125','green'),(4,'Utah O','9123','741-661-3125','red'),(5,'Hawkins M','6677','881-331-6612','blue'),(6,'James L','1266','934-513-5132','yellow'),(7,'Brian H','2244','785-513-9821','green'),(8,'David J','1155','960-231-5151','black'),(9,'Hawkins M','6677','135-661-3516','red');

Any help would be great, im using mysql 5.5