In MySQL i want to find difference between the two rows in same table
where i need to display the unmatched records
Here is my Table named project_details
CREATE TABLE IF NOT EXISTS `project_details` (
`project_name` varchar(100) NOT NULL,
`project_detail` varchar(100) NOT NULL,
`project_version` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `project_details` (`project_name`, `project_detail`, `project_version`) VALUES
('project_1', 'php_mysql', '1.0'),
('project_1', 'php_mysql_android', '2.0'),
('project_1', 'php_mysql_android_ajax', '3.0'),
('project_2', 'php_mysql', '1.0'),
('project_2', 'php_mysql_android', '2.0'),
('project_2', 'php_mysql_android_ajax', '3.0'),
('project_2', 'php_mysql_jquery', '1.0'),
('project_2', 'php_mysql', '4.0');
This is how table looks like
where i want the result of unmatched records from the table
that is from comparing project_1 and project_2
need to Compare both project_1 and project_2 from project_name then it should get the unmatched records of project_detail and project_version
i need result something like this displaying only unmatched records refer below
pic
解决方案
Use NOT EXISTS to retrieve output where for different project names there are no matching rows based on detail and version:
select *
from project_details p1
where not exists (
select 1
from project_details p2
where p1.project_name <> p2.project_name
and p1.project_detail = p2.project_detail
and p1.project_version = p2.project_version
)
Output
project_name project_detail project_version
---------------------------------------------------
project_2 php_mysql_jquery 1.0
project_2 php_mysql 4.0