0

This question has been asked at here as well.

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

tableA:

enter image description here

tableB:

enter image description here

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

  • 1
    Can you provide how the resulting table should look like when Morris Q is counted? – Kondybas Feb 1 at 3:32
2

I hope that query is selfexplanatory enough.

SELECT a.*, b.*
  FROM 
       (SELECT MAX(w.id) AS id
             , w.social_num
          FROM tableA AS w
         GROUP BY w.social_num
       ) AS maxa
  LEFT JOIN 
       (SELECT MAX(z.id) AS id
             , z.social_num
          FROM tableB AS z
         GROUP BY z.social_num
       ) AS maxb  ON maxb.social_num = maxa.social_num
  JOIN tableA       AS a  ON a.id = maxa.id
  LEFT JOIN tableB  AS b  ON b.id = maxb.id
;

The only assumption I've made is that persons from tableB are the subset of persons in the tableA and there is no persons listed in the table B and not in the tableA. Otherwise the FULL OUTER JOIN not implemented by mysql should be emulated by UNION.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.