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
Best Answer
I hope that query is selfexplanatory enough.
The only assumption I've made is that persons from
tableB
are the subset of persons in thetableA
and there is no persons listed in thetable B
and not in thetableA
. Otherwise theFULL OUTER JOIN
not implemented by mysql should be emulated byUNION
.